 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008eliminate data entry of counts by state
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 Post a SMALL example of the data tables you are using. HTH, Bernie MS Excel MVP > Hello > I receive numerous excel spreadsheets from vendors. Each provides [quoted text clipped - 8 lines] > > Thank you 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 > Post a SMALL example of the data tables you are using. > [quoted text clipped - 14 lines] > > > > Thank you 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  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Here is a sample after I've entered the state counts by hand that I've > received from Ingrum, Sourced Interlink and Msolve [quoted text clipped - 14 lines] > HI 6 > IA 23 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.