Excel question

The Sandbox Software/Technology
1

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.

2

Start with a single table with two columns:

Original Company Name New Company Name
Company B Company XY
Company C Company YZ
etc. etc.

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.

3

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.

2 Likes
4

:rofl:

5

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.

6

Select the data → Insert\PivotTable → choose 1 for rows, 2 for columns, and 3 for values. I think. Might need some tweaking, Pivots usually do.

7

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.

Or, Just “Copy”, “Paste-Special” “Transpose”

1 Like
8

Assume the new table is placed in columns D:N where you have 25 row-headers and 10 col-headers.

=SUMPRODUCT(($A$2:$A$251 = $D2)*($B$2:$B$251 = E$1)*$C$2:$C$251)

9

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.

10

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:

image
image696×77 2.58 KB

When I use my formula, the lower portion of the clip above doesn’t appear. Is there a way to get that functionality (pun intended)?

12

The function that you’re describing is, I think, “Formula AutoComplete”, under File, Options, Formulas.

However, AFAIK, you have to be editing the formula to get the visual tip.

13

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.

14

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.

15

Just found out I have support for Lambda in my Excel. May try that route to learn something new.