Forum Discussion
Using Indexing solution with formula to merge two sheets of data into a single sheet help
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!
- The source file was sent over with lots of bugs in the VBA, so the best way to get the data was to import it with PQ. I'm trying to use the cable sheet and match the raceway sheet together—the cable sheet acts as the definitions of the 'cable numbers' column for the raceway sheet. If you sort the 'TrayRaceway' sheet 'Line No' Column smallest to greatest this may show a better idea of what I'm trying to do...