B
Bill Dedman
I'm stumped on how to use VLOOKUP when there are multiple
matches, and I want them all, one after another.
I'm modifying a previous effort, adding one complexity.
The background: With help from Microsoft's Ray Hu, I was
able to set up an Excel file to call data from one page in
a worksheet into many pages in the same file (using OFFSET
to indicate the correct line in the original table), and
then to save as HTML each of those many pages with a macro.
An example of that Excel file is in:
http://powerreporting.com/files/knight1/census-o-matic.zip
An example of the HTML output from that file is at
http://www.suntimes.com/census/cities/addison.html
Now I'm trying to do something quite similar, but just
different enough to stump me.
I have a file of facts about newspaper companies.
That file would be similar to
http://powerreporting.com/files/knight1/diversity1.zip
I can make that file the data sheet in my workbook, and on
my report sheet in the same workbook I can call cells from
the right row in the data sheet, using OFFSET or perhaps
VLOOKUP. That brings in the newspaper name, circulation
total, etc., arranging them in a nice report.
At this point, my new example is no different than the old
project. There was one data row in my data sheet, from
which I used cell references and OFFSET to arrange
information as I wanted on my report sheet.
But here's the twist: On another sheet in my workbook, I
have facts on each ZIP Code where the newspaper sells
papers.
That file is
http://powerreporting.com/files/knight1/ZIPs3.zip
Each row is a ZIP Code, showing the newspaper ID number,
ZIP Code, number of sales, and demographics for the ZIP. I
want to pull that information into rows at the bottom of
my report sheet:
ZIP City State Sales Income Race Etc.
I believe I understand how to use VLOOKUP on my report
sheet, to look down the ID column on the data sheet, find
the newspaper ID number matching the current newspaper's
ID that I'm reporting on, and pull in the ZIP data on that
row.
But how do I deal with the fact that newspapers report
many ZIPs, even thousands, and I want to show them all on
my report. Do I need somehow to use VLOOKUP or another
function to pull in the data from the first row matching
that newspaper ID, then to pull in the data from the next
row matching that ID, until there are no more, arranging
that information repetitively down my report sheet.
I can order the data rows in the ZIP data sheet in the
order that I will want them to appear in the report sheet
(ordered by newspaper, then, say, ZIPs ordered by sales
descending.
But how do I get five rows of ZIP data on one of my
reports, and 10 on the next, or however many exist in the
ZIP table?
I thought of setting up an IF, where we check for the
first row that has a matching ID, using VLOOKUP. Then down
the report page I check for whether the row below that has
the same member number, and if it does, I pull in that
info with cell references; if not, then it's just a blank
cell. Does that seem like the right way to go at this? I'm
barely literate with VLOOKUP.
This seems like a job for a macro or VB, but that's beyond
me. I could use your help on this.
Thanks for your help.
Bill Dedman
matches, and I want them all, one after another.
I'm modifying a previous effort, adding one complexity.
The background: With help from Microsoft's Ray Hu, I was
able to set up an Excel file to call data from one page in
a worksheet into many pages in the same file (using OFFSET
to indicate the correct line in the original table), and
then to save as HTML each of those many pages with a macro.
An example of that Excel file is in:
http://powerreporting.com/files/knight1/census-o-matic.zip
An example of the HTML output from that file is at
http://www.suntimes.com/census/cities/addison.html
Now I'm trying to do something quite similar, but just
different enough to stump me.
I have a file of facts about newspaper companies.
That file would be similar to
http://powerreporting.com/files/knight1/diversity1.zip
I can make that file the data sheet in my workbook, and on
my report sheet in the same workbook I can call cells from
the right row in the data sheet, using OFFSET or perhaps
VLOOKUP. That brings in the newspaper name, circulation
total, etc., arranging them in a nice report.
At this point, my new example is no different than the old
project. There was one data row in my data sheet, from
which I used cell references and OFFSET to arrange
information as I wanted on my report sheet.
But here's the twist: On another sheet in my workbook, I
have facts on each ZIP Code where the newspaper sells
papers.
That file is
http://powerreporting.com/files/knight1/ZIPs3.zip
Each row is a ZIP Code, showing the newspaper ID number,
ZIP Code, number of sales, and demographics for the ZIP. I
want to pull that information into rows at the bottom of
my report sheet:
ZIP City State Sales Income Race Etc.
I believe I understand how to use VLOOKUP on my report
sheet, to look down the ID column on the data sheet, find
the newspaper ID number matching the current newspaper's
ID that I'm reporting on, and pull in the ZIP data on that
row.
But how do I deal with the fact that newspapers report
many ZIPs, even thousands, and I want to show them all on
my report. Do I need somehow to use VLOOKUP or another
function to pull in the data from the first row matching
that newspaper ID, then to pull in the data from the next
row matching that ID, until there are no more, arranging
that information repetitively down my report sheet.
I can order the data rows in the ZIP data sheet in the
order that I will want them to appear in the report sheet
(ordered by newspaper, then, say, ZIPs ordered by sales
descending.
But how do I get five rows of ZIP data on one of my
reports, and 10 on the next, or however many exist in the
ZIP table?
I thought of setting up an IF, where we check for the
first row that has a matching ID, using VLOOKUP. Then down
the report page I check for whether the row below that has
the same member number, and if it does, I pull in that
info with cell references; if not, then it's just a blank
cell. Does that seem like the right way to go at this? I'm
barely literate with VLOOKUP.
This seems like a job for a macro or VB, but that's beyond
me. I could use your help on this.
Thanks for your help.
Bill Dedman