eliminate data entry of counts by state

[redhead]

Hello
I receive numerous excel spreadsheets from vendors. Each provides
distribution by state, but not all vendors distribute in each of the 50
states. I enter the spreadsheets data into one spreadsheet to obtain total
distribution by state for an annual report.

I would like to import the data by state with a macro or formula that tells
Excel if state = empty then skip or enter zero.

I want to eliminate entering this data by hand.

Thank you

 

[Bernie Deitrick]

Post a SMALL example of the data tables you are using.

HTH,
Bernie
MS Excel MVP

 

[redhead]

Here is a sample after I've entered the state counts by hand that I've
received from Ingrum, Sourced Interlink and Msolve
Thank you

DEC 07 ISSUE STATE Ingrum Sourced Interlink Msolve
AK 42 15
AL 31 64
AR 34 5 8
AZ 44
CA 386 384
CO 53 50
CT 64
DC
DE 4 21
FL 111 206
GA 56 54 45
HI 6
IA 23

 

[Max]

One formulas play to tinker with

Illustrated in this sample:
http://www.freefilehosting.net/download/3bb31
Count by state from various sheets.xls

Assume states are listed in col A in each of the source sheets from the
vendors, with source sheets named after the vendor, ie: Ingrum, Sourced
Interlink, Msolve

In Summary,
The complete list of states are assumed in A2 down
Enter the vendors' sheetnames in B1 across,
eg: Ingrum, Sourced Interlink, Msolve

Then put in B2:
=COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A2)
Copy B2 across/fill down to populate the required counts from each vendor's
sheet