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 B/Company XY
Company C/Company YZ
Company YZ/Company RR
Company RR/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 C/CompanyYZ/CompanyRR/Company AA
Company RR/COmpany AA
Company YZ/Company RR/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.
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.