Combining Multiple Tabs to one with array functions and removing blank rows

So I have an old spreadsheet that I use in google sheets. It has several tabs and then on a master tab it uses a function that allows me to combined all of the other tabs and ignore blank rows. My new company doesn't allow google sheets, so I'm trying to figure out how to do this in Excel Online (so I can't really use VBA).

I was hoping someone knew if the new array functions would allow me to do something like this?

So my sample data would like like this:

Sheet1:

Name Age
David 10
Rachel 12
Sarah 3

Sheet2:

Name Age
Rebecca 2

Angela 38

Master sheet:
Name Age
David 10
Rachel 12
Sarah 3
Name Age
Rebecca 2
Angela 38

You could set up a running count of the number of valid records on each sheet - the count continues from one sheet to another.

Then in the Master sheet it is quite easy to retrieve those records.

Attach a sample file so I can show you how to set it up.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

In real life it's about 20 - 30 tabs. Each tabs is a different user input so it can vary during the year.

How about attaching a sample file?

Pete

Example attached

I'm just going out soon, but to get you started you should enter zero in cell C1 of the first subsidiary sheet (user1) and then this formula in C2:

=IF(OR(A2="",B2=""),"-",MAX(C$1:C1)+1)

Copy this down as far as you like (the hyphens will show where the formula is active, in case you add more data in future). The formula generates a sequential number for each record which meets the criteria - we want to have that numbering continue on into the next sheet, so in cell C1 of the user2 sheet you can have this formula:

=MAX(user1!C:C)

i.e. that formula links back to the previous sheet and returns the largest value from column C of that sheet. Then you can use the same formula in C2 as in the previous sheet, i.e.:

=IF(OR(A2="",B2=""),"-",MAX(C$1:C1)+1)

Again, copy this down as far as you need to in order to cover the data. You can see that the numbering on user1 sheet of your example covered 1, 2, 3, and that this continues as 4 and 5 in the next sheet. If you have further sheets, then you use the same formula in C2 copied down, but in C1 the formula points back to the previous sheet.

I need to go now, but I'll get back to you later on.

Hope this helps.

Pete

Well, that was a long interlude - late getting back, then in the garden all day today.

If you have followed Part 1 in Post #7 you will realise that formulae in column C of every subsidiary sheet set up a series of sequential numbers for appropriate records throughout those sheets. You can have as many sheets as you like, as long as the starting value for each sheet (i.e. C1) looks back at the previous sheet to find out where the numbering had got to. What we need to do now in the Master sheet is to find out in which sheet those numbers occur, and on what row - let's use columns C and D in the main sheet for that.

We can find the sheet name where a particular number occurs by means of a table, so list all your sheet names in column E, starting in E2. Put a zero in cell F1 (important), then you can have this formula in cell F2, against the first name:

=IFERROR(MAX(INDIRECT("'"&E2&"'!c:c")),"")

Copy this down for each sheet in column E, and it will tell you where the numbering had reached in each subsidiary sheet. Then in C2 you can use this formula:

=IF(ROWS($1:1)>MAX(F:F),"",INDEX(E:E,MATCH(ROWS($1:1)-1,F:F)+1))

The ROWS($1:1) term will generate sequential numbers on successive rows as it is copied down, so this will give you the sheet name where those numbers can be found. Put this formula in cell D2:

=IF(C2="","",MATCH(ROWS($1:1),INDIRECT("'"&C2&"'!c:c"),0))

and copy this down - it will give you the row number where the record can be found. Then you can use this formula in A2:

=IF($C2="","",INDEX(INDIRECT("'"&$C2&"'!a:b"),$D2,COLUMNS($A:A)))

to get the name, and the formula can also be copied into B2 to get the Age, Copy these two formulae down as far as you need to.

I've set this up for you in the attached workbook. I've added two extra sheets (user3 and user4) with some made-up data, so you can more easily see how to apply this approach with more sheets. In each of these sheets I've copied the formula in C2 down to row 10, and in the user3 sheet this shows up a problem if the formulae are not copied far enough to cover your data. I have a record for Jane in row 12, but as the formula is not copied down that far her record down not have a number in column C, and hence the record will not appear on the Master sheet.

In the Master sheet, I have copied the formulae in cells A2:D2 down to row 20, which is sufficent in this example to pick up all the data (cell F5 shows that there are 12 records expected), though obviously you should copy down further if you have more data. The helper columns (C in the subsidiary sheets, and C to F in the Master sheet) can all be hidden if you don't want to see them in your real file.

Well, that's it - hope you have found this detailed explanation helpful, so you can apply it to your real file.

Pete

I really appreciate it, this works perfectly!