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.