Forum Discussion
Using Indexing solution with formula to merge two sheets of data into a single sheet help
al_baum Not sure I. understand what you are trying to achieve by "indexing" sheets. You talk about wanting to merge two sheets. Then, I assume you have two sets of data where each set has at least one common column (unique references) by which the two can be merged/joined E.g. a product number or client ID or transaction code, whatever. You can use functions like VLOOKUP, XLOOKUP, INDEX/MATCH, or FILTER, but without seeing the data it would be difficult to construct a formula for you.
So, if you could upload the workbook, or at least a representative portion of it, that would be great. Remove/replace any private and/or confidential information though.
Riny_van_Eekelen, thanks for your response! My first attempt was to add a 'line no.' column at the beginning of the first sheet, then sort the column with multiple variables in ascending order. After that, I created the formula to assign an Id to each variable and then converted the formula into a value. (The initial 'line no.' column was created to revert the sheet to its original state.) On the second sheet with the variable definitions, I did the same thing. By doing this, will I still be able to add the variable definitions from the second sheet to each variable on the first? I hope this makes sense. If not, ill need to clean some things up before I can share...
al_baum Sorry. Difficult to visualise what's needed. can you upload a workbook?
Riny_van_Eekelen Thanks for all your assistance with this! Here is the file; I'm trying to figure out how to take the information from the cable sheet and match it to the raceway sheet information. For instance, I'm looking to match the 'cable id' to the 'cable numbers' in both sheets. However, the cable sheet has duplicate cable titles with different information in each row. Is it possible to also match these rows to the correct 'raceway tag' column?
So far, I've added 'Line No' Columns to revert the sheet to its original state, then sorted the cable tag columns in each sheet and created a new ID column titled Cable ID. I intended to merge the two sheets using this 'cable ID' column I created. However, there are multiple 'cable ID' columns that have different values. Hopefully, this makes sense.
al_baum You seem to have worked with Power Query. Unfortunately, I can't connect to your source file(s) and I can't see where both tables come from and how they were constructed. But when I copy both sheets values to new tables, they seem to be identical. So, still not sure what you want to achieve.
I do notice that quite many Cable IDs have two rows, like your line no 2 and 3. But with some missing information on the second line. So, Column 8, line 3 is blank. Would you want the value from the row above to be copied down? If so, select column 8, choose the Transform ribbon, Replace values (nothing with null) and Fill Down. Not sure, though, that this is what you need. That's why you need to explain what needs to be done, a bit more clear. Sorry!