Not really excel, since I use Libreoffice, but similiar.
I have a list of companies that have changed their names or merged throughout the years. It’s a pretty large list. Here’s what I have:
Company/Becomes
Company A
Company B/Company XY
Company C/Company YZ
Company D
Company XY
Company YZ/Company RR
Company RR/Company AA
Company AA
So if you follow it through, company C became COmpany YZ. COmpany YZ became company RR. Company RR is now COmpany AA since company aa is on the list and hasn’t merged or changed it’s name.
What’s the best way to end up with a list like
Company A
Company B/CompanyXY
Company C/CompanyYZ/CompanyRR/Company AA
Company D
Company XY
Company RR/COmpany AA
Company YZ/Company RR/Company AA
Company AA
i.e. so I can pick any ocmpany on the list and have linked to it the company that it is now.
I last used Excel for anything useful about 1999, so go gentle.
If you can, perhaps create a flag for subsequent duplicates in the above table.
Then in a separate table, you’ll have several columns:\
Name_0
Name_1
Name_2
. . .
Name_i
Company A
Company B
Company XY
Company C
Company YZ
CompanyRR
. . .
Company AA
etc.
etc.
etc.
The formula you would use for column “Name_k” would be (this is an Excel formula):
=IFERROR(VLOOKKUP(“Name_(k-1)”,LOOKUP_TABLE_ABOVE,2,false), “Name_(k-1)”)
Where “Name_(k-1)” refers to the cell immediately to the left and “LOOKUP_TABLE_ABOVE” is the reference to the entire table created above.
Then you can reference this entire table and grab the right-most-column (“Name_i”) for the result.
Thanks! I’ll have a look at it tonite.
It was originally a grunt-work job for my spouse but I showed them how to do one and got told that it was a job for me lol.
I feel like I should know how to do this but I am having struggling for some reason today.
I have a data set with 3 columns. Columns 1 has letters, column 2 has #s, and column 3 has a value. I want to convert into a table with the letters (data from column 1) as row headers and #s (data from column 2) as column headers and the values from column 3 are shown in the corresponding cell.
I created the table with appropriate row and column headers but I can’t think of the appropriate formula to lookup the data in the data set to correctly identify the row to pull the value from. Surely there is a relatively simple formula to do that.
SUMPRODUCT is your friend.
Q1: Is this a one-time thing or will it be ongoing?
Q2: if ongoing, will the number of combinations of Columns 1 and 2 change?
Q3: Are there multiple rows with the same columns 1 and 2 combinations? If so, Do you want those separate or added together? If separate, what makes them different (another field, probably)?
You can make your new headers as concatenations or as separate rows (Row 1 for Columns A, Row 2 for Column B). If concatenation, you’ll need to add a column in the original as a concatenation of Columns A and B.
I don’t use pivot tables all that much. I tried it and couldn’t figure out how to get the actual values, just statistics like sum, average, etc.
1 time thing (just a quick and dirty look at some data) with unique C1 & C2 combinations - which makes me realize I could have added a 4th column that concatenates the 2 and does a lookup based on that column
That’s the one. I don’t work in XL nearly as much as I used to and apparently some of the skills have been lost.
Didn’t want to start a new thread, so since this is an Excel question, thought it would fit here.
I created a UDF and it takes 4 arguments. When I start using the formula in my spreadsheet, I’d like to see the list of arguments appear as I start entering values. Excel functions do this, but since I don’t know what this is called, I am having trouble figuring out if I can get this functionality as well.
So if I use XLOOKUP, after entering the opening paren, I get this:
It’s my understanding Excel will not produce a tool tip for UDFs. The top reply at Excel VBA UDF Tool-tip/hint box has two decent options though—either hit Ctrl+Shift+a to have the arguments appear in the formula, or register the function so the function wizard can help.
The functionality you want is now in the new Lambda functions. Define your function using Lambdas instead of UDFs and the parameters will show up as you type in the cell.
