Creating a pivot table using aggregate counters and flags

Hello,

See the attached workbook:

I can use assistance from the group here in building the below from my sample data.

I need to accomplish a few things here and can use assistance:
- Create a VLOOKUP to count the number of matches with the below criteria.
In “Sheet 1” compare the value in Column H to the “List” tab (Column A). When the value in Column H matches (regardless of case) to the “List Tab”, AND Column I = “Reformat” output a ‘Y’, else a ‘N’ in Column J.
In “Sheet 1” compare the value in Column H to the “List” tab (Column A). When the value in Column H matches (regardless of case) to the “List Tab”, AND Column I = “Select1” OR “Select2” output a ‘Y’, else a ‘N’ in Column K.
In “Sheet 2” compare the value in Column H to the “List” tab (Column A). When the value in Column H matches (regardless of case) to the “List Tab”, AND Column I = “Reformat” output a ‘Y’, else a ‘N’ in Column J.
In “Sheet 2” compare the value in Column H to the “List” tab (Column A). When the value in Column H matches (regardless of case) to the “List Tab”, AND Column I = “Select1” OR “Select2” output a ‘Y’, else a ‘N’ in Column K.

- Create a counter to aggregate the number of “Y’ values when Column A has the same value.
In “Sheet 1”, when Column A has the same value, count the number of values = “Y” in Column J, and aggregate that number in Column L.
In “Sheet 1”, when Column A has the same value, count the number of values = “Y” in Column K, and aggregate that number in Column M.

- Create a Pivot Table to show the results.
See the “Results” Tab. I need to display a unique list of Column A from BOTH tabs (Sheet 1 and Sheet 2). I should display Column D, and then the aggregate counters from Column L and M from the above calculations. I need this to be a pivot so that I can click on the value to see what values from Column H matched.

Thank you in advance!!

Good Morning everyone, just wanted to see if anyone can assist with my questions.!!!!

Thank you! At first glance, this appears to work for Column J and K for Sheet1 and Sheet2.

How would I do the next steps?

- Create a counter to aggregate the number of “Y’ values when Column A has the same value.
In “Sheet 1”, when Column A has the same value, count the number of values = “Y” in Column J, and aggregate that number in Column L.
In “Sheet 1”, when Column A has the same value, count the number of values = “Y” in Column K, and aggregate that number in Column M.

- Create a Pivot Table to show the results.
See the “Results” Tab. I need to display a unique list of Column A from BOTH tabs (Sheet 1 and Sheet 2). I should display Column D, and then the aggregate counters from Column L and M from the above calculations. I need this to be a pivot so that I can click on the value to see what values from Column H matched.

Morning check for any assistance on the 2nd part of my questions.

Thank you!

One issue with the above formula.

In 'Sheet2', Column A has the same value (103990). I would need that counter (Column L and M) to account for the aggregate instances of 'Y' Values.

So, as an example, since Column A in sheet2 has the same value (103990) and Column H returned a 'Y' value in both columns, I would like to see a value of '2' returned instead of 1.

How would I create a pivot table (see 'Results' tab) that accounts for both Sheet1 and Sheet2?

Any feedback on my above questions (and the pivot table using two sheets)?

Originally Posted by JeteMc

Going back to post #1:
In “Sheet 1” compare the value in Column H to the “List” tab (Column A). When the value in Column H matches (regardless of case) to the “List Tab”, AND Column I = “Reformat” output a ‘Y’, else a ‘N’ in Column J.
Looking at the file attached to that post, on sheet 1, cell J5 is marked 'N' although hi02s is found in the 'List' and I5 displays 'Reformat'
Supposing that to be an error the following formula displays the same answers in all other cases:

Formula:

Please Login or Register  to view this content.

As to a formula for column K the instructions say to find the terms 'Select1' or 'Select2' in column I, however the term used in sheet 2 is 'Selection' so should the formula attempt to find only 'Select' or should the formula attempt to find a term with 'Select' included?
I'll wait to get some clarification before proceeding.
Let us know if you have any questions.

Correct, that was a mistake on my part; the formula should look for Select1 or Select2. Thank you for pointing that out.

Originally Posted by JeteMc

That being the case a formula that will work for column K is:

Formula:

Please Login or Register  to view this content.

I now have another question. The next instruction states: In “Sheet 1”, when Column A has the same value, count the number of values = “Y” in Column J, and aggregate that number in Column L. Can we assume that it means the same value for the same customer?
Let us know if you have any questions.

That's correct - Column A and Column D would have the same value for as long as Column A does not change.
Although Column A has the same value, Column D could have the same Customer values repeated.

For example:

Column A has different values, but could (not in the attached example though) have the same Customer name.
103,801
103,801
103,982
103,982

Originally Posted by JeteMc

So if I were to change Customer C to Customer A on sheet 1 that would set up the same scenario that you describe. I don't see any change in the values displayed in columns J:M. Is that as expected?

Yes, that seems to be working as expected (changing Customer A to Customer C. As long as Column A is used to differentiate when Column C has the same value, this is working well from the testing.

My last question then is how to display this in the 'Results' tab.

- Create a Pivot Table to show the results.
See the “Results” Tab. I need to display a unique list of Column A from BOTH tabs (Sheet 1 and Sheet 2). I should display Column D, and then the aggregate counters from Column L and M from the above calculations. I need this to be a pivot so that I can click on the value to see what values from Column H matched.

Originally Posted by JeteMc

As to the pivot table question I believe that the issue is that of a unique key that would need to appear on both sheets 1 and 2 and I don't see any. However, I have a work around that might be of interest. Look at sheet 3 and you'll see that it is populated by formulas so that it will update automatically as sheets 1 and 2 are updated, providing the formulas are copied far enough down. The two formulas for sheet 3 are:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The pivot table on sheet PT then uses the data on sheet 3.
Note: To arrange the customer names in alphabetical order on the pivot table they need to be in the left column (primary key).
Let us know if you have any questions.

Thank you! I will try to use your suggestion on my larger data and will let you know should I have any questions.

One minor update - for 'sheet 3', when there is no value, can you update the blank to a '0'?

Originally Posted by JeteMc

Yes. Modify the second formula on sheet 3, which is applied to B1:N22 in the file attached to post #18, so that it reads:

Formula:

Please Login or Register  to view this content.

Note: This will necessitate the need to filter out the zero in the 'Customer' column on the pivot table.
Let us know if you have any questions.

For my previous question, I was referring to the counters in H and I (when blank, output a zero) only.

One last question for now. and thank you for your time. Very helpful.

Is it possible, in Sheet 3, to not display a row if both Column H AND I are = 'N'? The reason I am asking is my actual data will have many rows and I wouldn't need to review scenario's where both are equal to 'N'.