Comparing Data

[han77729]

Hello all,

I'm trying to compare a POS data from last month to POS data from this month. (I have both POS data in the same workbook, in separate sheets) These POS data consist of multiple columns as such (Customer, part number, part type, shipping date, etc..) What I'm trying to do is identify the new part numbers that the existing customers from last month are buying, from this month's data.

I've done a similar command before where I'm locating new customers from the new POS data comparing to the old POS data.(and it works!)

This is the formula I used =IF(ISNA(MATCH(Table1[[#This Row],[Customer]],ExistingCustomer,9)),"NewCustomer","Old")

Maybe this formula will five you a direction of what I'm trying to do, which is comparing. Comparing current part numbers that doesn't overlap previous month's part number for each customer. (To identify new part # old customers are ordering)

Hope this makes sense,

Han

[Ken Puls]

Hi Han,

FYI, I nuked your other thread, as it was a duplicate. I do appreciate that you posted in the PivotTable forum with the other thread, but no need to post it in two place. We can deal with it here.

Can you do us a favour and upload some sample data that we can look at? Mock it up, or do a find/replace to remove anything sensitive. The closer it is to reality the more it will help us determine the right path though.

[han77729]

Hi Han,

FYI, I nuked your other thread, as it was a duplicate. I do appreciate that you posted in the PivotTable forum with the other thread, but no need to post it in two place. We can deal with it here.

Can you do us a favour and upload some sample data that we can look at? Mock it up, or do a find/replace to remove anything sensitive. The closer it is to reality the more it will help us determine the right path though.

Ken,

Apologies for the repeated thread. Sorry that I'm only getting back to you now, I hope you enjoyed your holidays. Please view attached excel file for the POS data I'm referring to. Again, I want to identify what new part numbers existing customers are ordering.

If you have any questions or confused about what I'm asking please let me know.

Thanks again,

Han

ComparingDataForExcel.xlsx

[Ken Puls]

Hey there,

Woah! Okay, first thing's first. You have to resize your data tables. You've got them covering the entire column, which means that they are covering (and calculating formulas on) 1048576 rows! In my tests I hung Excel a few times before I figured that out.

Go to Table Tools-->Design-->Resize Table, and make sure that you only cover the data range that actually has data ($A$1:$E$1886 on October POS, and $A$1:$F$2044 on December POS).

With that done, this is actually pretty easy, so long as you're okay with adding an "Ordered Parts" column to the October table, using the following formula:

=[@Customer]&"-"&[@[Part Number]]

From there, you can go to the December POS worksheet and add a "New or Old Part?" column:

=IF(ISNA(MATCH([@Customer]&"-"&[@[Part Number]],Table2[Ordered Parts],0)),"New Part!","Previously Ordered")

Workbook attached,

[han77729]

Hey there,

Woah! Okay, first thing's first. You have to resize your data tables. You've got them covering the entire column, which means that they are covering (and calculating formulas on) 1048576 rows! In my tests I hung Excel a few times before I figured that out.

Go to Table Tools-->Design-->Resize Table, and make sure that you only cover the data range that actually has data ($A$1:$E$1886 on October POS, and $A$1:$F$2044 on December POS).

With that done, this is actually pretty easy, so long as you're okay with adding an "Ordered Parts" column to the October table, using the following formula:

=[@Customer]&"-"&[@[Part Number]]

From there, you can go to the December POS worksheet and add a "New or Old Part?" column:

=IF(ISNA(MATCH([@Customer]&"-"&[@[Part Number]],Table2[Ordered Parts],0)),"New Part!","Previously Ordered")

Workbook attached,

Ken,

WOW! Thanks for the in-depth explanation on this matter. I will use this and I'm sure it'll be a great addition for the analysis!

Appreciate your help as always,

Han