Lines filling

[Khalil Portugal]

Hello! I have two big worksheets, with data from several ID's and their info (each line refers to one subject, the first column is the subject's ID and the next ones, his info. Each sheet has different info about the subjects). I need to merge them into one (putting together the subject and all info I have on him, from both sheets), but not all the ID's from one sheet appears on the other one (and vice-versa). Is there a way to automaticaly do it (There's an image explaining what i need to do)?

I had the idea of expanding both sheets in sequential ID numbers (my ID's are not sequential, but i could interpolate them with sequential numbers) and then I could just copy/paste one sheet into another. But still I don't know how to make this interpolation (as seen on the image)

Thanks for the attention!

Khalil

[#2]

Hi Khalil,

Power Query would be best for this. I would apply the following steps:

1. Load both source tables into Power Query 

2. Merge the tables using 'Full Outer' merge 

3. Expand all columns to show all columns from all tables 

4. Add a column with a condition to extract a value from either of the index columns where the index value exists

5. Remove columns other that the combined index, Neg/Pos and New/Old

6. Move the index column to the left

7. Sort the table ascending by Index

8. Replace null values in both Neg/Pos and New/Old columns with empty spaces

9. Load the resulting merge table into a spreadsheet. Do not load the queries from the source tables, just keep them as connections.

If source table values have changed, refresh the merged table by right-clicking on the table and hitting 'Refresh'

Please see attached an example. You will need to install Power Query if you have Excel versions 2010 or 2013. Power Query is embedded in Excel 2016+.

Hope this helps

Yury

[#3]

I'll try it! Thanks a lot!