Search results

The trick is to import is as "cells". So instead of trying to glean the meaning from the sheet, just create a table like this. Workbook, Sheet, Row , Cell, Value, Data_type You then do all the manipulation in SQL. Excel is notorious at presenting incorrect datatypes to DTS because it...

Concatenate is the same as A1& B1 etc...but I think (technically) supports more characters. VLookup is very common, but technically INDEX and MATCH run quicker because MS have optimized them. However on the VLOOKUP front, when you use joins in Power Bi, vlookup will be dead to you. It doesn't...

Have a look at Power Bi (built into Excel) it can connect to almost anything, but simply you put your "data" in one sheet(s) and connect and manipulate it. You don't need to write formulas for 99% percent of Excel tasks. Take for your examples "Vlookup" or "Proper". You'll never use them...

I wish I had that power, but SQL (or whatever) with Excel front end with power bi definitely the way forward. I don't do formulas any more - because (as you know with power bi) you don't have too.... For individual projects, I usually like installing SQL Express on client machines and let them...

Nothing worse than a vanity "look how clever I am" formula that no-one else can decipher. It's moronic. I'd also apply that to 99% of macros which can easily be achieved with in-built functionality and formulas. The most stupid I've seen (and I've seen a lot) is a macro that writes formulas...

My solution attached. The columns in yellow you will effectively hide. Just to reiterate, breaking formulas into smaller chunks (and hiding columns if you want) - isn't lazy or dumb. Far from it, it is best practice and I was taught that by the best. A very well known city accountancy firm...

The only difference with the solution I liked, it that I'd use "anchoring" to get at the column header (e.g. "2") rather than hard coding it into the formula.

TEXTJOIN is fine, but only introduced relatively recently to Excel.

That's what I would do and it's a nice solution to break it down and hide columns. There is nothing clever about long formulas, nothing at all. To get rid of the trailing comma, you could reference X2 in the next column with =IF(LEN(X2)>0, LEFT(X2 , LEN(X2)-1),"") But only if you were...