Forum Discussion
Compiling two worksheets - help please!
I need to compile data from two worksheets. I need them to match up data based off an order number that is in both worksheets, and the data associated with that order number from both worksheets be added to a single row in a master worksheet. Help please!
If I understood right, in each sheet you have the same index in a certain column (let's say A) and you need to sum datas in the same row.
So, you have to use SUMIF; in a third worksheet (the one with the totals) you should have:
column A: index number (same for the 2 single worksheet)
column B: VLOOKUP(index;worksheet;2;false) if you have a name of the row or whatever you want to display to remember what are you talking about in that row
column C (that is B if you don't need the B I stated): SUMIF(worksheet1 indexes;index;worksheet1 column to sum)+SUMIF(worksheet2 indexes;index;worksheet2 column to sum).
see attachement
I didn't explain very well. I don't want anything to sum together. So, both worksheets have an order number listed. I need everything associated with a specific order number to combine on to one row in a new worksheet. But in that row, each value from the first two worksheets still needs its own column.
That could be done with Power Query:
- name data range for first sheet (e.g. "RangeOne") and for second one;
- query both ranges in separate connection query, filter in each empty records;
- merge both queries (inner join) and land the result in new sheet.
Please see attached.
