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

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!

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

11 Replies

Riny_van_Eekelen
Platinum Contributor
Mar 30, 2021
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.
- al_baum
  Copper Contributor
  Mar 30, 2021
  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...
  - Riny_van_Eekelen
    Platinum Contributor
    Mar 30, 2021
    al_baum Sorry. Difficult to visualise what's needed. can you upload a workbook?

Forum Discussion

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

11 Replies

Resources