need macro code or any function to consolidate the data with counts

**girishhb** · 01-26-2014, 09:21 AM

Hi Team,

Need macro code or any function to consolidate the data by count wise in excel. Below the data how i need.

Sheet 1 - Summary tab
Sheet 2 - order processed
Sheet 3 - order completed

Sheet 2 contains below data:

date order
processed name
01/25/2014 123 MR Xyz
01/25/2014 321 MR Xyz
01/25/2014 456 MR Zxy
01/26/2014 789 MR Zxy
01/26/2014 1011 MR Zxy
01/26/2014 1213 MR XyZ
01/27/2014 1314 Mr XYZ

Sheet 3 Contains data below:

date order
completed name
01/25/2014 1231 MR Zxy
01/25/2014 3212 MR Xyz
01/25/2014 4563 MR Zxy
01/26/2014 7894 MR XyZ
01/26/2014 10115 MR Zxy
01/26/2014 12136 MR XyZ
01/27/2014 13147 Mr XYZ

I need the data below in Summary sheet.(it would be better if i get date also so that i can get the production of whole month for an employees, if i select the particular date I should get the count of employee per day for both order processed and completed)

Name count of order processed count of order completed
MR XYZ
Mr ZXY

Please let us know if you need further information. Thank you very much for your help in advance.

Regards,
Girish

**Richard Buttrey** · 01-26-2014, 10:01 AM

Hi,

The best advice I can offer is have one sheet with a single which contains both orders processed and order completed. The fields for the table would be

Date
Name
Order Ref
Order State 'which would contain either "P" or "C" for Processed or completed.

Then use a Pivot table to get the desired output.

**girishhb** · 01-26-2014, 10:44 AM

thanks for the reply, i need to auto capture the data in summary sheet if the employee update the data in sheet 1 & sheet 2 since if i do pivot table i need to work it manually daily. Please let us know if their any function,

i have few more columns in sheet 1 and sheet 2 so i can't merge both order processed and order completion in one sheet.

**Richard Buttrey** · 01-26-2014, 12:17 PM

Originally Posted by girishhb

thanks for the reply, i need to auto capture the data in summary sheet if the employee update the data in sheet 1 & sheet 2 since if i do pivot table i need to work it manually daily. Please let us know if their any function,

i have few more columns in sheet 1 and sheet 2 so i can't merge both order processed and order completion in one sheet.

I'm not saying merge the sheets (as they are). What I am saying though is bring all the data onto one sheet. And if one sheet has more columns than another, no matter. Just create a single sheet with as many unique fields as necessary. Some fields may be blank for some records - but that's fine.

You really should bring all this together on one sheet and use a PT.