Create summary of three different sheets

[esbencito]

Hi all,

I need to build a recap sheet that combines all three buying sheets into one. Basically, clients would input available styles/items and then decide which ones to buy. Now, in order to run some analytics on the total buy I want to combine all three while neglecting items that are listed but weren't bought as well as the blank rows inbetween.

Instead of copying row by row (actual dataset is 100x larger), I thought this might be solvable with an array formula that is inserted in every cell and copies the cell from sheet BUY 1 if conditions are met and moves on to BUY 2 once all bought styles (without the blanks) were inserted. Not sure how to explain this better. The attached sample file should be a lot more clear...

[esbencito]

Just to be more clear, ROWS and COLUMNS never change! They are and will always be the same for all 4 sheets!

[Pete_UK]

You can do this with a few formulae. First of all, put zero in cell V1 of the BUY_1 sheet and this formula in V2:

=IF(SUM(S2:U2)>0,MAX(V$1:V1)+1,"-")

and copy this down to the bottom of your table - it will identify the records which match your criteria and give each a unique sequential number. We want to continue that numbering into the other sheets, so in cell V1 of BUY_2 you can use this formula:

=MAX('BUY 1'!V:V)

and then you can use the previous formula in V2, copied down. Similarly, in V1 of the BUY_3 sheet use this formula:

=MAX('BUY 2'!V:V)

and this one in V2:

=IF(SUM(S2:U2)>0,MAX(V$1:V1)+1,"-")

copied down. In the RECAP sheet we can produce a summary table of the number of records in each of the subsidiary sheets, so list the sheet names BUY 1, BUY 2 and BUY 3 in cells AA2 to AA4, put zero in cell AB1 (this is important), and this formula in cell AB2:

=MAX(INDIRECT("'"&AA2&"'!v:v"))

Copy this down to AB4.

It will also help if we use two more helper columns in this sheet, one for the sheet name and the other for the row in that sheet where the data can be found, so put this formula in W2:

=IFERROR(INDEX($AA$2:$AA$4,MATCH(ROWS($1:1)-1,$AB$1:$AB$4)),"")

and this one in X2:

=IF(W2="","",MATCH(ROWS($1:1),INDIRECT("'"&W2&"'!v:v")))

Copy these down to the bottom of your table. Then you can use this formula in cell A2:

=IF($W2="","",INDEX(INDIRECT("'"&$W2&"'!a:u"),$X2,COLUMNS($A:A)))

This formula can be copied across to U2, and then the formulae from A2:U2 can be copied down to the bottom of your table. You might need to re-instate any conditional formatting that you might have set up.

Hope this helps.

Pete

[JohnTopley]

Based on formula here:

https://excelxor.com/2014/10/16/coll...ons/#more-1482

Named range "arry1"

Please Login or Register  to view this content.

Name range "Sheets"

={"Buy 1","Buy 2","Buy 3"}

In sheet "Test"

in X1 (count of number of rows of valid data from 3 sheets)

Please Login or Register  to view this content.

in A2

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across and down

Change max range of 1000 in all formulae to your chosen value

[esbencito]

You can do this with a few formulae.

Hope this helps.

Pete

Thank you Pete!

This is an excellent solution. It does seem to work just fine. Though, I probably should have mentioned that there aren't actually three sheets, but instead three workbooks on a shared drive. Hence, I'm not sure if this would be easily maintainable with formulas such as

Please Login or Register  to view this content.

etc. as the order of workbooks might change or one gets deleted?

[esbencito]

Based on formula here:

https://excelxor.com/2014/10/16/coll...ons/#more-1482

Named range "arry1"

Please Login or Register  to view this content.

Name range "Sheets"

={"Buy 1","Buy 2","Buy 3"}

In sheet "Test"

in X1 (count of number of rows of valid data from 3 sheets)

Please Login or Register  to view this content.

in A2

Please Login or Register  to view this content.

Thanks John!!

Learning something new every day! Never heard of a three-dimensional array before! Thanks also for sharing the link with a more detailed explanation, very insightful! Generally speaking, this seems to be the perfect solution, though, there are two main problems:

1. How can I adjust the formula to only consider items where all three (BUY UNIT, BUY RETAIL and BUY COST) are >0? Currently it only checks one column not the SUM of all three?

2. The data actually comes from three different workbooks on a shared drive rather than just sheets. Is such an array formula feasible with specifying the sheet AND workbook?? when I tried to adjust the named range "sheets" to

Please Login or Register  to view this content.

it did not seem to work as it returned a #REF error...

[JohnTopley]

As I am not the author of the solution offered, I cannot say if it will work over workbooks: my suspicion is no.

For this (BUY UNIT, BUY RETAIL and BUY COST) > 0 I think we will need a "helper" column to do the sum although in your sample all 3 are always present.

[sandy666]

maybe look at PowerQuery (Get&Transform) built-in in Ex2016 (PC not Mac)

[Pete_UK]

... there aren't actually three sheets, but instead three workbooks on a shared drive. ...

If your data is in three external files, then I don't think my solution (or that of John's) will work, as they both use the INDIRECT function to choose the sheet where the data is located, and if that is in an external file the function does not work with closed fiels.

Pete

[sandy666]

is that what you want?
Done with PowerQuery (Get&Transform)
doesn't matter workbooks are open or not - if source data is in another workbooks

[JohnTopley]

@Sandy,

The results don't meet the criteria (BUY UNIT, BUY RETAIL and BUY COST) > 0. But I am sure you can add in this condition!

John

[esbencito]

As I am not the author of the solution offered, I cannot say if it will work over workbooks: my suspicion is no.

For this (BUY UNIT, BUY RETAIL and BUY COST) > 0 I think we will need a "helper" column to do the sum although in your sample all 3 are always present.

I have asked the author, let's see what he's got to say about this. but I assume you're right, with the INDIRECT especially, it probably won't be feasible!

[esbencito]

If your data is in three external files, then I don't think my solution (or that of John's) will work, as they both use the INDIRECT function to choose the sheet where the data is located, and if that is in an external file the function does not work with closed fiels.

Pete

I have managed to adjust your method to make it work with external files as well! I'm now just linking the data from each file and I then replaced your INDIRECT with nested IFs and INDEX/MATCH! works just fine, though, the links are slowing down the workbook significantly!

Thanks again!

[esbencito]

is that what you want?
Done with PowerQuery (Get&Transform)
doesn't matter workbooks are open or not - if source data is in another workbooks

brilliant! Probably even better as performance seems to be improved! Just need to test it with all external workbooks, but hopefully should be ok! Thanks!

[sandy666]

@John,
Sure but now I need a nap a little. Maybe evening will be a good time to re-read everything from the top
or
maybe OP will get any formula solution in a meantime

sandy

[esbencito]

@John,
Sure but now I need a nap a little. Maybe evening will be a good time to re-read everything from the top
or
maybe OP will get any formula solution in a meantime

sandy

Don't you just have to filter out the 0s and blanks when editing the Query and then save? That's what I just did...

[sandy666]

Maybe, I didn't read in details this thread. Just joined 3 sheets together. But I see you solved problem with criteria (whatever they are )

[sandy666]

Ok, here is