Combination of IF and VLOOKUP maybe?

[Beckerby]

Hi All,

I need to report how many times we used Promo Code X, Y, or Z for how many of each product A, B, C...

Data-set will contain about 10,000 records each month and whatever solution I find will need to be run on up to 5 Promos per month.

Here is my quandry:
If the current record's 'prod_id' = "107", I need to look at each record where 'invh_no' is same as current record and 'data_field' is equal to current record's ('data_field' * -1.0) and then report a total 'qty' of each 'prod_id' = "107" broken down by the 'prod_desc' from those matching records.

It seems so confusing explained this way but here is some example data and what the results should be:

[TABLE="class: grid, width: 786"]
<colgroup><col width="131" style="width:98pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 131"]invh_date[/TD]
[TD="width: 131"]invh_no[/TD]
[TD="width: 131"]quantity[/TD]
[TD="width: 131"]prod_id[/TD]
[TD="width: 131"]prod_desc[/TD]
[TD="width: 131"]data_field[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2017[/TD]
[TD]561712[/TD]
[TD="align: right"]4[/TD]
[TD]107[/TD]
[TD]Promo X[/TD]
[TD] -31.95[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2017[/TD]
[TD]561712[/TD]
[TD="align: right"]1[/TD]
[TD]550[/TD]
[TD]Delivery[/TD]
[TD] 2.45[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2017[/TD]
[TD]561712[/TD]
[TD="align: right"]8[/TD]
[TD]AAAA[/TD]
[TD]Product A[/TD]
[TD] 31.95[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564131[/TD]
[TD="align: right"]2[/TD]
[TD]107[/TD]
[TD]Promo X[/TD]
[TD] -21.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564131[/TD]
[TD="align: right"]1[/TD]
[TD]550[/TD]
[TD]Delivery[/TD]
[TD] 2.00[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564131[/TD]
[TD="align: right"]4[/TD]
[TD]BBBB[/TD]
[TD]Product B[/TD]
[TD] 15.00[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564131[/TD]
[TD="align: right"]4[/TD]
[TD]CCCC[/TD]
[TD]Product C[/TD]
[TD] 21.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564250[/TD]
[TD="align: right"]2[/TD]
[TD]107[/TD]
[TD]Promo X[/TD]
[TD] -30.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564250[/TD]
[TD="align: right"]1[/TD]
[TD]550[/TD]
[TD]Delivery[/TD]
[TD] 2.45[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2017[/TD]
[TD]564250[/TD]
[TD="align: right"]4[/TD]
[TD]DDDD[/TD]
[TD]Product D[/TD]
[TD] 30.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]2[/TD]
[TD]107[/TD]
[TD]Promo X[/TD]
[TD] -16.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]1[/TD]
[TD]550[/TD]
[TD]Delivery[/TD]
[TD] 2.45[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]4[/TD]
[TD]BBBB[/TD]
[TD]Product B[/TD]
[TD] 15.00[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD]Product E[/TD]
[TD] 45.00[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]1[/TD]
[TD]FFFF[/TD]
[TD]Product F[/TD]
[TD] 45.00[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[TD]Product A[/TD]
[TD] 16.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD]569703[/TD]
[TD="align: right"]1[/TD]
[TD]HHHH[/TD]
[TD]Product H[/TD]
[TD] 30.50[/TD]
[/TR]
</tbody>[/TABLE]


Promo X (107) =
Product A = 6
Product C = 2
Product D = 2

Hope that isn't too terribly vague - Any help would be greatly appreciated...

 

[StephenCrump]

Promo X (107) =
Product A = 6
Product C = 2
Product D = 2

Welcome to the Forum!

Can you please explain how you calculate these totals, for the data you've posted?

 

[Beyond_avarice]

Use a pivot table.

 

[inactiveuserps07]

@Beckerby, I understand what you're trying to do, and I believe it's possible. What is full the range of your data, including headers (starting with the cell that holds the heading invh_date)?

 

[Beckerby]

Hi Erik,

I am starting to realize that I might qualify as a "beginner" in comparison to the experience/knowledge level of other users on this forum and I first want to express my heartfelt gratitude for your willingness to help me out...

Now, sadly, I must admit that I thought the table above was my "full range of data" so I am not certain how to answer.

What I imagined I needed was a formula of some sort that would be inserted into a new column to the right of 'data_field' where the result would contain/display the contents of the 'prod_desc' column from any record matching the other combined criteria (same 'invh_no' and same 'data_field' but displayed as a positive/negative).

StephenCrump asked for more explanation of the totals I listed in the example - If you number the sample with the first row of data as record number 1:

Record 1 would display the 'prod_desc' from record 3 (Product A)
Record 2 would display N/A or False
Record 3 would display the 'prod_desc' from record 1 (Promo x)
Record 4 would display the 'prod_desc' from record 7 (Product C)
Record 5 & 6 would display N/A or False
Record 7 would display the 'prod_desc' from record 4 (Promo x)
Record 8 would display the 'prod_desc' from record 10 (Product D)
Record 9 would display N/A or False
Record 10 would display the 'prod_desc' from record 8 (Promo x)
Record 11 would display the 'prod_desc' from record 16 (Product A)
Records 12 – 15 would display N/A or False
Record 16 would display the 'prod_desc' from record 11 (Promo x)
Records 17 would display N/A or False

I then sort all rows by (a) ‘prod_id’ and then by (b) the new column. I report the total of the ‘quantity’ column for each product description reported in the new column (within each ‘prod_id’ that is a Promo code).

I would just ignore all records where the new column reports a “Promo” of any kind or N/A.

In essence, I report the total of the quantity in records 1 & 11 (4 & 2 respectively) as a total of (6) “Product A” that were involved in “Promo x”.

Sorry if my posts are too long & involved – I am just not sure how much detail is helpful.

Thanks again!

 

[inactiveuserps07]

@Beckerby, we were all beginners at some point; and we are all still (hopefully) learning. Never worry about that.

Now ... I think you may be over-complicating things.

A couple more questions for you, and then I'll be able to suggest a solution:

1. Just to be sure, you currently have only six columns of information per row, correct?

2. You mentioned you may have up to 5 promo codes per month. How many total unique products will you have?

 

[Beckerby]

Thanks for the encouragement - I really appreciate it.

1. Yes, after deleting about 35 columns of irrelevant data and re-ordering the columns, this will be all that is left.
2. There could be up to 65 unique product descriptions in the full set of data.

 

[inactiveuserps07]

@Beckerby, OK, here's what I'd suggest you do:

1. I'm assuming your existing headers are in A1:F1. In G1, add a new header: combo_help

2. Once that's added, format your seven columns as a table. To do that, select all seven headers as well as all data currently below them (with the understanding that Column G will be blank). Click to open the Insert tab on the ribbon, then click "Table." Be sure that "My table has headers" is checked and click "OK."

3. In cell G2, enter the following formula, which will now copy itself down the column automatically, since it's part of an official table:

=A2&B2&E2&ABS(F2)

4. Leave Column H blank for some spacing. In cell I2 and below, enter your product names as they appear in Column E (prod_desc). So, using your sample data, in cell I2, you'd enter Product A, in I3 Product B, etc. It's important that your product names match what's in Column E exactly.

5. In cell J1, enter the name of your first product code, again exactly as it appears in Column E (prod_desc). Do not include the number from Column D, just the name from Column E. So, using your sample data, J1 would hold Promo X. Additional promo names should be entered in K1, L1, M1, N1, etc., as needed.

6. In cell J2, enter the following formula:

=IFERROR(SUMPRODUCT(--($E$2:$E$20000=J$1)*($C$2:$C$20000)*(ISNUMBER(MATCH(SUBSTITUTE($G$2:$G$20000,J$1,$I2),$G$2:$G$20000,0)))),"")

Drag-copy this down Column J as far as needed (i.e., so that there is a formula beside every product name.

7. When you're ready, select the entire range of formulas in Column J (i.e., J2 through J???), and drag-copy to the right so that the formula populates under each promo code name.

I currently have the main formula set to process 20000 rows. If you won't need that many, you can improve performance by cutting all instances of 20000 to fit your actual data range. For instance, if you wind up with 10000 products in that month's list, you only need the formula to have a range of, say, 10500 instead of 20000, which will double processing speed.

 

[Beckerby]

Thanks a Million!!! It appears to be working...

I now have the names of the formulas involved and I can now go learn more to understand how exactly it is working.

Again, really appreciate you!

 

[inactiveuserps07]

Happy to have helped.

If you run into trouble understanding the formula, let me know.

Also, if you don't want to see Column G, just select the whole column (by clicking the "G" at the top), right-click, and choose Hide.