How can I display the names without duplicates and the numbers for each person going across in columns.
#solved
Excel Question:
If I have the follow data (attached pic). How can I display the names without duplicates and the numbers for each person going across in columns.
For example:
Mike 5 8 5
David 8 7
Replies
Mike Cornish refer image below using MS365 formula
• Formula used in cell D2
=LET(_name,A2:A18, IFERROR(UNIQUE(HSTACK(A2:A18, MAKEARRAY(ROWS(_name),MAX(COUNTIF(A2:A18,A2:A18)), LAMBDA(r,c,INDEX(FILTER(B2:B18,_name=INDEX(_name,r)),c))))),""))
Mayukh Bhattacharya that formula seems very impressive. I'll definitely try it out. Thanks for your assistance, wow 👌
Mike Cornish Sir, you are excel consultant so you know more than me!!!
Mayukh Bhattacharya knowing some things is one thing, haven't met someone who's a master of Excel, yet. Thanks again
Mike Cornish Sir, tbh, saw some of your solutions work which gives inspiration and helped to learn as well. thank you very much sir
Mayukh Bhattacharya I have used your formula to remove all duplicate names. Now how do I transpose the numbers to the right of each name?
Mike Cornish sorry i havent got any notification, just saw now. Also it has been solved now.
=LET(x,UNIQUE(A2:A10),HSTACK(x,MAP(x,LAMBDA(lm,TEXTJOIN(" ",TRUE,FILTER(B2:B10,A2:A10=lm))))))
what is your excel version?
NaBeel GonDal 365
Mike Cornish
=+UNIQUE(C2:C8) =TEXTJOIN(" ",,FILTER($D$2:$D$8,E2=$C$2:$C$8))
set ranges
Mike Cornish Why you delay & waste time. There several solutions but depends on structure. Show your expected result in Excel format so that we can give you the answers.
Biplab Das why are you suggesting I'm wasting time? The answers I have to received are good but are version dependant
Mike Cornish We want to see output sample in Excel Format because Solution depends on output Structure
Biplab Das the output will be; Column 1 Name, Column 2 Result 1, Column 3 Result 2, Column 4 Result 4....
Like I said I need to see what Result is most suitable depending on excel version for the end user
Mike Cornish same as this ?
Mike Cornish Use Simple Pivot Table. It can do all very easily
Sample Attached.
Biplab Das yes this did work, thank you
Mike Cornish Edit Post & Write #Solved in TOP of the post
#Solved
Thanks guys for your replies, I am working through the best approach unfortunately I'm working with a few computers with different versions of Excel...
Mike Cornish Sir, if there is no issue, then I think Power Query can be used as well, which is applicable from Windows Excel 2010+ onwards, however if the end user is very much new to excel, then i think using a combination of IF(), COUNTIF() & VLOOKUP() can also be used.
