SOLVED
Home

summarize purchases by client, product and date

%3CLINGO-SUB%20id%3D%22lingo-sub-778913%22%20slang%3D%22en-US%22%3Esummarize%20purchases%20by%20client%2C%20product%20and%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778913%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20multiple%20raw%20for%20each%20purchase%20of%20every%20client%20(data%20are%20sorted%20by%20client%20number%20then%20by%20date%20of%20purchase)%3C%2FP%3E%3CP%3EFor%20instance%2C%20client%20number%20one%20purchased%205%20times%20and%203%20different%20products%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20which%20product%20he%20bought%20firstly%2C%20secondly%20and%20so%20on.%3C%2FP%3E%3CP%3EThe%20output%20I%20want%20is%20in%20green%20just%20below%20and%20my%20input%20is%20the%20table%20on%20the%20left%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124909iBB59438B946786AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel.PNG%22%20title%3D%22excel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3ESalvador%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-778913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779281%22%20slang%3D%22en-US%22%3ERe%3A%20summarize%20purchases%20by%20client%2C%20product%20and%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384236%22%20target%3D%22_blank%22%3E%40salvador97%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20this%20array%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24C%242%3A%24C%2412%2CINDEX(MATCH(%24E2%26amp%3BSMALL(IF(%24E2%3D%24A%242%3A%24A%2412%2C%24B%242%3A%24B%2412%2C%22%22)%2CCOLUMN(A1))%2C%24A%242%3A%24A%2412%26amp%3B%24B%242%3A%24B%2412%2C0)%2C))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124932iE1BCCE803A9C92F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Summary.png%22%20title%3D%22Summary.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20have%20to%20press%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Enot%20only%20Enter%20each%20time%20you%20enter%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20it%20out%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779312%22%20slang%3D%22en-US%22%3ERe%3A%20summarize%20purchases%20by%20client%2C%20product%20and%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20world%20needs%20geniuses%20like%20you.%20If%20you%20come%20in%20France%2C%20just%20let%20me%20know%2C%20I%20will%20pay%20you%20a%20beer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20a%20good%20day%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESalvador%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hi,

I have multiple raw for each purchase of every client (data are sorted by client number then by date of purchase)

For instance, client number one purchased 5 times and 3 different products

I would like to know which product he bought firstly, secondly and so on.

The output I want is in green just below and my input is the table on the left below

excel.PNG

 

Thanks in advance,

Salvador

2 Replies
Solution

@Deleted

 

Hi,

 

You need this array formula:

=IFERROR(INDEX($C$2:$C$12,INDEX(MATCH($E2&SMALL(IF($E2=$A$2:$A$12,$B$2:$B$12,""),COLUMN(A1)),$A$2:$A$12&$B$2:$B$12,0),)),"")

Summary.png

 

But you have to press Ctrl+Shift+Enter not only Enter each time you enter this formula.

 

Please check it out in the attached file.

@Haytham Amairah thank you so much.

 

The world needs geniuses like you. If you come in France, just let me know, I will pay you a beer.

 

have a good day,

 

Salvador

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies