Comparing different worksheets

[jchichuv]

ok, this is the issue, i work normally with 3 or 4 different worksheets of inventory, one of the company that i work for, a shelter company, the one from customs and from the warehouse and i have to compare them, match them, know if they have different quantities or values and have all the information in a single worksheet.

for example lets call them worksheet 1,2, 3 and 4.

in worksheet 1 I have the same item twice in different rows and different quantities, in total lets say 3568.

In worksheet 2 i have that item only once and it says 3472.

in the 3rd i have the same item with 3568 but its on the first row.

and on the 4th i have it with 3500.

In my worksheet i have to know what inventory my company says that we have and the one that the shelter company said we have and show them the difference, if we need an adjustment.

can some one help me with this

 

[dourpil]

Hi,

Can you upload a sample of your file? It'll help helpers help you :]

 

[jchichuv]

[TABLE="width: 1372"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]#Material[/TD]
[TD]Description[/TD]
[TD]Unit
Cost[/TD]
[TD]WEC INVENTORY[/TD]
[TD]QTY SCRAP WEC[/TD]
[TD]PART MASTER INVENTORY SONI[/TD]
[TD]ACTION[/TD]
[TD]SONI SCRAP TO ADJUST[/TD]
[TD]UofM[/TD]
[/TR]
[TR]
[TD]H09640000000000[/TD]
[TD]CLIP,COPPER[/TD]
[TD="align: right"]0.012367[/TD]
[TD="align: right"]385,800.000000[/TD]
[TD="align: right"]-231.000000[/TD]
[TD="align: right"]2,429,383.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-2,043,583.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]99-3227[/TD]
[TD]PROTO INSULATOR[/TD]
[TD="align: right"]0.132212[/TD]
[TD="align: right"]31,080.000000[/TD]
[TD="align: right"]-460.000000[/TD]
[TD="align: right"]129,906.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-98,826.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]003220003000000[/TD]
[TD]CONTACT[/TD]
[TD="align: right"]0.085105[/TD]
[TD="align: right"]60,000.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]130,183.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-70,183.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]VN321203[/TD]
[TD]CABLE 3 COND 12AWG (56/28) 4MM H07RN-F[/TD]
[TD="align: right"]1.0552[/TD]
[TD="align: right"]61,202.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]118,191.27[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-56,989.27[/TD]
[TD]Feet[/TD]
[/TR]
[TR]
[TD]99-1048GE15[/TD]
[TD]BODY PLATED[/TD]
[TD="align: right"]0.190700[/TD]
[TD="align: right"]32,109.000000[/TD]
[TD="align: right"]-3,327.000000[/TD]
[TD="align: right"]85,484.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-53,375.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]106511000095000[/TD]
[TD]PIN CONTACT ASSEMBLY PLATED[/TD]
[TD="align: right"]0.240440[/TD]
[TD="align: right"]6,024.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]55,491.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-49,467.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]BX1067[/TD]
[TD]WHITE BAG 4" X 6"[/TD]
[TD="align: right"]0.020374[/TD]
[TD="align: right"]203,041.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]246,119.79[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-43,078.79[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]99-0352[/TD]
[TD]PROTO SPRING WASHER[/TD]
[TD="align: right"]0.018000[/TD]
[TD="align: right"]250,000.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]284,916.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-34,916.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]A-59-102-1 M06[/TD]
[TD]NUT, COUPLING[/TD]
[TD="align: right"]0.555120[/TD]
[TD="align: right"]17,836.000000[/TD]
[TD="align: right"]-13.000000[/TD]
[TD="align: right"]49,942.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-32,106.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]306-B-1 M88[/TD]
[TD]SOLDER PREFORM[/TD]
[TD="align: right"]0.007573[/TD]
[TD="align: right"]6,891.000000[/TD]
[TD="align: right"]-425.000000[/TD]
[TD="align: right"]36,010.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-29,119.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]MA0048[/TD]
[TD]LABEL THRML 3" X 1.5" BLA[/TD]
[TD="align: right"]0.003590[/TD]
[TD="align: right"]39,097.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]64,975.68[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-25,878.68[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]1-7779-1 M05[/TD]
[TD]CONTACT[/TD]
[TD="align: right"]0.095283[/TD]
[TD="align: right"]10,000.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]33,000.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-23,000.00[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]CN104496[/TD]
[TD]CONTACT[/TD]
[TD="align: right"]0.009376[/TD]
[TD="align: right"]75,000.000000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]95,881.51[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-20,881.51[/TD]
[TD]Each[/TD]
[/TR]
[TR]
[TD]MA0135[/TD]
[TD]LABEL WRAP AROUND 1 X 3.75[/TD]
[TD="align: right"]0.040515[/TD]
[TD="align: right"]5,854.000000[/TD]
[TD="align: right"]-1,804.000000[/TD]
[TD="align: right"]26,719.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-20,865.00[/TD]
[TD]Each[/TD]
[/TR]
</tbody>[/TABLE]

 

[Slizer6893]

From what I understand you need a summary sheet of all unique items?

Sounds like the first worksheet would be a sumif on the item

The second, third, and fourth. If I understand correctly there will only be one instance of each item so a SUMIF or Index(match) or even a vlookup can be used.

I have no idea your cell references or how much data you are looking at but here is a shot in the dark

Summary Sheet

All items in Column A, Row 1 would include the worksheet data its coming from...warehouse...vendor,etc

For worksheet one it sounds like a simple sumif works

=sumif(Worksheet1'D:D,A2,A:A)

for the rest I would use a sumif still to be safe change the references?

Then create another set of 4 columns that compares differences to your inventory count?