Using Indexing solution with formula to merge two sheets of data into a single sheet help

[al_baum]

Hi, 

I'm trying to find a way to merge two sheets. Sheet1 has all the variables to which I've tried to assign an index number. Sheet 2 has the definitions of all the variables from sheet 1.

My process has been to Filter the column in sheet 1 and then use the =IF(B3=B2,A2,A2=1) to assign a value to each cell. I've indexed Sheet 2 and have all the values assigned to each row. However, I'm having trouble with 1: clearing the filter on Sheet1 and the value assigned for each row changing 2: cannot merge the two sheets without the proper index values. I'm not sure if I'm doing this correctly. I greatly appreciate any help with this as I've spent several days trying to wrangle this dataset.

*edit, Using Windows10 OS, I believe Excel v2012 or whichever Office365 comes with

[#2]

@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.

[#3]

@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...

[#4]

@al_baum Sorry. Difficult to visualise what's needed. can you upload a workbook?

[#5]

@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.  

[#6]

@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!

[#7]

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...

[#8]

@al_baum Sorry! You've lost me. Both sheets have 2089 rows and, at a glance they seem to be identical.

[#9]

@Riny_van_Eekelen, hmm...that weird; I've uploaded a new one. Try this one...

[#10]

@al_baum Ah, I must screwed up copying and pasting. Sorry. Will get back to you if not someone else does. Diner time for me now.

[#11]

Ah yes, all the times I've scratched my head by doing that :) Again, thanks for helping me with this!

[#12]

@al_baum Okay, I believe I managed to replicate your merge query of the two tables. Can sort the list as suggested but now what? Attached my working file with queries connecting to static tables within the workbook. What would the end result have to look like?