Extract Data from large file
I am working with a large file that I need to quickly extract data from. The attached file contains some raw data that I need to consolidate into a summary. The file has a columns called RA#, Store #, Material, Order QTY, and some others that I do not need to worry about. I need Excel to combine the same RA#, Store # and material numbers on the file to get me a total amount of each material that was returned for each RA#. Please note that the data on the file is collected daily and the files may have over 900 lines on the file.
Thank you for helping
| Attachment | Size |
|---|---|
| Data.xlsx | 14.15 KB |
Hi Gmadd, There is no
Hi Gmadd,
There is no attached File, however a pivot table would probably be best for your needs.
Go to Insert ? Pivot Table ? Choose Range & where you wish it to appear ? put RA# in filter, Material in Row Labels & Quantity in Values.
You can click on values and select Value field settings to change count to sum or anything else you may find appropriate.
To update your table once you have made changes to your Source Data just right click on the pivot table and select Refresh
Regards
Mark
I tried a pivot table and it
I tried a pivot table and it kind of works but my end result needs to be displayed like below because I am uploading this file into another system and I cannot figure out how to make a pivot table display how I need it. I just attached the file I hope you can see it this time and I have it displayed on the attachment on how it needs to be.
Store Material Order qty UoM RA #
7247 38125ESS 3 EA 60178173
7247 05846AQU 2 EA 60178173
7247 26342BIO 3 EA 60171864
Thanks for getting back to me
Hi Gmadd, in the attached if
Hi Gmadd,
in the attached if you put the following code in cell Q12 and copy down it will give you the total order quantity:
=SUMPRODUCT((C$12:C200=S12)*(E$12:E200=O12)*(F$12:F200=P12)*(H$12:H200))
If your source data exceeds row 200 obviously increase the range in above function.
to get the rest of the stuff in place just copy and paste, then sort and remove duplicates
Hope this helps
Regards
Mark