VLOOKUP to create list of rows on each worksheet
The Tip of the Night for December 13, 2016 concerned VBA code that you can use to generate a list of the ranges of cells with data on all worksheets in a workbooks. Once you have the ranges generated on a worksheet you may then want to create sets of formulas on a master worksheet that enters all of the date from the original worksheet.
You can go about this in the following manner.
Parse out the ranges (using the Text to Columns tool) for the data on each worksheet so you have the last row on each worksheet in a column. (In this example see column H). In the column to the right use a simple addition formula to add the number of rows from the preceding worksheet to those from the next worksheet, so that we end up with the grand total number of rows on each worksheet. (In this example see cell I5). Then put the names of each worksheet in the column to the right. (in this example see column J).
In column K generate a list of numbers from 1 to whatever the total number of rows on all worksheets is. In this example we have from 1 to 280204.
Now we're going to use a modified form of the standard VLOOKUP formula to generate cell entries containing the name of each worksheet so that each entry corresponds to the number of rows on the worksheet. To this enter the VLOOKUP formula like this:
=VLOOKUP(K2,I:J,2)
So when the row number looked up in column K reaches the row number that the next worksheet begins on in the total count for all rows it generates a reference to that worksheet name. The key is to omit the FALSE or TRUE reference usually included at the end of a VLOOKUP formula.
The goal here is to generate a list of HLOOKUP formulas in multiple cells on the master worksheet which will pull the data from each successive worksheet.
Generate in column M numbers keeping track of the row count for each worksheet. Just use a formula like this:
=IF(L3<>L2,1,M2+1)
Enter a 1 in the top cell, this formula in the next cell and then pull it down.
Now we're ready to edit the resulting data so that we can generate HLOOKUP formulas which will search across the the column headings in row A of each worksheet and pull the resulting data to the master worksheet. So we enter text like this in NotePad ++:
Sheet3 1 Pitching 1 Pitching 2 Pitching 3 Pitching 4 Pitching 5 Pitching 6 Pitching 7 Pitching 8 Pitching 9 Pitching 10 Pitching 11 Pitching 12
. . . and then edit it so we end up with:
=HLOOKUP(A$1,Sheet3!$1:$17100,ROW(A1),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A1),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A2),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A3),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A4),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A5),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A6),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A7),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A8),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A9),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A10),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A11),FALSE) =HLOOKUP(A$1,Pitching!$1:$17100,ROW(A12),FALSE)
The end of the range should correspond to the highest row number with data on any of the worksheet, in this example 171000. Enter the column headings from one worksheet in row A on the master worksheet. Then enter in the formula created in NotePad ++, or another text editor, in column A, and pull over the formulas with CTRL + R, so the data is filled in each column with a matching heading.
