Excel Magic Trick 690: Extract Unique Records Based on 2 Columns, List Values Horizontally

Everything is perfect but i don't want the values listed in the columns instead i need the sum of it. Do you a solution for this?

Thanks for this and all of your other videos. I am trying to use this technique but am having issues listing all the unique records when there is duplicate records between the unique records. How would you suggest changing the formula to only return the row reference of the unique records rather than all the records and taking the first two references listed?

Hi Mike, pls help me with this. I've data entry table in a sheet which has the header "Date,Party,LotNo.,Pieces,Weight,%,Chemical,Qty Consumed". I need to get unique list of Chemicals based on Party and Lot No.Thanks Akbar

One of my favourite videos! Really shows the power of formulas! Thanks, Mike!

Thanks, Mike. My head is spinning but your explanation is wonderful. Keep it up!

Thank you Mike. Your explanation is awesome. I am sure lots of people will benefit from your videos.

i am using this formula in my data.. but the problem is i have dates(dd/mm/yy) insted of products.. so data is like sales rep, sales_date, total_sales... i want same kind of MIS as explain in this video.. but i want monthly MIS.. the problem is i dont knw how to extract month from date and create an arrey out of it. for eg. in E8 i want "Quin-March'12" n so on.. any way to do this without adding column to extract months?

I am using this formular but the logical test for the first if keeps telling me it will return the #VALUE which impacts on the rest of the formular which appears to calculate ok when i check it using 'show calculation steps'. Any sugestions as to why it is doing this? I have checked that the format cell is all set the same as in your example.

I have a few videos on sorting formulas. Serach for "sort Formula Excel".

hi, sir. thank you very much for your help--you are awesome. i have one more question. if i am only dealing with the count column, and no items column, is there a way to get a sumproduct formula that will automatically sort from the highest number?

Yes, but it very complicated: and the comments section will not let me post formula. I'll e-mail it to you.

question for you excel master. i am using the following formula: =IF(C:C="y",IF(B:B=1,A1,""),"") on the following cells: A B C jon 1 y jon 0 y jon 0 n bob 1 y bob 0 n mat 1 y it returns: jon bob mat when dragged down in column D, starting at row 1. is there a way to return the results without the linebreaks/empty rows???? THANKS!

Sorry to hurt your head! ;)

Wow, my head hurts. Huge formula and great explanation. Thanks Mike.

You are welcome! This video was released before, but somehow got deleted so I had to upload it.

thank you Mike. It's an awesome video.