Results 1 to 5 of 5

Thread: Comparing Data

  1. #1

    Comparing Data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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,
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    Wow!

    Quote Originally Posted by Ken Puls View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •