Is there a formula for: If this equals that analyse array data

**KateW** · 11-02-2011, 07:22 PM

Dear Excel Gurus,

I am having wording issues for what I am trying to accomplish thus am not able to find solutions in the forums.

Please direct me if this is a duplicate.

I have two sheets of information. Both have headers of store numbers and product columns of productA, productB, and productC with quantities listed under each product.
I am trying to return data for the matching stores but the discrepancies of products A B C in three columns.

I’ve tried playing with match and vlookup and sumif; but am doing something wrong and not receiving accurate results.

Thank you in advance,
Kate

PS Updated with attachment

**MarvinP** · 11-02-2011, 07:43 PM

Hi Kate and welcome to the forum,

A small sample of the workbook with two sheets and how you have the data arranged would help solve this one.

You can attach a sample by clicking on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

**KateW** · 11-03-2011, 05:49 PM

Thank you Marvin!

**MarvinP** · 11-03-2011, 06:01 PM

Hi Kate,

I don't see that you need your sheet1 at all. It seems to me you can add a new store on Sheet2 and simply sort by store number. You can add a store or insert a row and do a store number.

I am a little concerned about the Effective Date and if this is a reporting date of what each store has. This might change and you would then want to use Max type of function to only look at the latest date for each store.

**KateW** · 11-03-2011, 07:09 PM

Hi Marvin,

Well the effective date makes no difference at this point. The difference between sheet 1 & 2 is that the stores are allocated different numbers of trees. That is why I am looking for formula help... how to produce a unified set of differences.
Something that matches the two sheets store numbers and analyses the three attached columns (trees). So that for store 405 the difference is -1 white tree. true IP. true LG IP.

Does that make more sense? I need to know what stores from sheet 2 got shipped vs sheet 1. AKA they used the outdated shipping log and I need to know what ones are getting extras or missing trees.

Thanks,
Kathryn

**MarvinP** · 11-03-2011, 07:19 PM

Hi Kathryn,

Dealing between two sheets is much harder than a single sheet. You should try to do single sheet types of tables and add data that allows you to analyse your data correctly. I don't really see how two sheets help answer your questions.

I guess I need help understanding what each sheet shows and how or why they might be different and why it takes two sheets to arrive at this info.