Forum Discussion
Compiling two worksheets - 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.
So it's easier!
In the third sheet you'll have the index number, then in a new column a VLOOKUP to look for that index in first sheet then in the next one the same VLOOKUP looking from the other sheet.
sheet1:
cell A1: 1
cell B1: valor1
sheet2:
cell A1: 1
cell B1: valor2
sheet3:
cell A1: 1
cell B1: VLOOKUP(A1;sheet1!$A$1:$B$100;2;false)
cell C1: VLOOKUP(A1;sheet2!$A$1:$B$100;2;false)
You may want to insert an IF(ISNA(VLOOKUP function in sheet3 B1 to avoid #N/D! errors shown:
sheet3:
cell A1: 1
cell B1: IF(ISNA(VLOOKUP(A1;sheet1!$A$1:$B$100;2;false));"";VLOOKUP(A1;sheet1!$A$1:$B$100;2;false))
cell C1: IF(ISNA(VLOOKUP(A1;sheet2!$A$1:$B$100;2;false));"";VLOOKUP(A1;sheet2!$A$1:$B$100;2;false))
This will show an empty cell if there is not a valor in the sheets (let's say if there is index 12 in sheet1 but not in sheet2, for some reason), just in case. If you change "" with 0 you'll have 0 instead of a blank cell, or you can add text inside quotes as "ALERT" or something like that.
