I want to know how to get repeated items in rows without duplication.
#solved
Thank you champs
#Help
Hello guys!
I'm new to this group and my field is supply chain.
I have a lot of queries.
Can you please help me out.
I want to know how to get repeated items in rows without duplication.
Example:
John A
Mickey B
Tailor C
John D
John K
Mickey Z
John A
I want to get row wise
Solution I want:
John A D K
Mike B Z
Tailor C
Here you can see John has 2 A's but I don't want duplication so it should show once.
Replies
Assuming you have office 365 (or a dynamic array version of excel that has these functions), I would first separate the names and values into separate columns (may be able to use text to columns feature).
Then, say the data is in A1:B7, you could try: =LET(names, UNIQUE(A1:A7), values, BYROW(TRANSPOSE(IF(A1:A7=TRANSPOSE(names), B1:B7, "")), LAMBDA(rw, TEXTJOIN(" ", TRUE, SORT(UNIQUE(rw, TRUE))))), names&" "&values)
Or, you might check out power query and see if this is something it could do.
If ur Excel is not 365 u can use this huge array formula.. Assumption: ur data is in column A, A1 contains the Column Name/Header, A2:A100 contains the names ~ I assume that ur data will not pass A100, though u can change all of this to whatever the cell reference of ur data..
Formula used: Extracting "UniqueFirstName" =INDEX(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),0),{0,0}),"")))))
Extracting "UniqueFullName" =INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),"")))))
Final Formula Construction =IFERROR(PROPER(INDEX(UniqueFirstName,ROWS(D$2:D2))&" "&TEXTJOIN(" ",,IFERROR(INDEX(RIGHT(UniqueFullName,1),N(IF(1,AGGREGATE(15,6,ROW(INDIRECT("1:"&COUNTA(UniqueFullName)))/(LEFT(UniqueFullName,LEN(UniqueFullName)-2)=INDEX(UniqueFirstName,ROWS(F$2:F2))),ROW(INDIRECT("1:"&COUNTA(UniqueFullName))))))),""))),"")
Replacing the "UniqueFirstName" & "UniqueFullName" with its actual formula, combining all together ~ Final Solution =IFERROR(PROPER(INDEX(INDEX(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),0),{0,0}),""))))),ROWS(D$2:D2))&" "&TEXTJOIN(" ",,IFERROR(INDEX(RIGHT(INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),""))))),1),N(IF(1,AGGREGATE(15,6,ROW(INDIRECT("1:"&COUNTA(INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),""))))))))/(LEFT(INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),""))))),LEN(INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),""))))))-2)=INDEX(INDEX(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH(LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),LEFT($A$2:$A$100,LEN(TRIM($A$2:$A$100))-2),0),{0,0}),""))))),ROWS(D$2:D2))),ROW(INDIRECT("1:"&COUNTA(INDEX(TRIM($A$2:$A$100),N(IF(1,MODE.MULT(IFNA(MATCH(ROW($A$2:$A$100)-ROW($A$1),MATCH($A$2:$A$100,$A$2:$A$100,0),{0,0}),"")))))))))))),""))),"")
Note: u must enter the formula using CTRL+SHIFT+ENTER then drag it down to how many cells u want.
No no, you didn't understand. All are written on different cell so I don't need to separate them. Like A B 1 Name category 2 Johnny A 3 Johnny. B
So no need to separate cells.
Maya Edward Show Screenshot in Excel Format instead of Words
This is what I want, here you can see John has multiple category and one category is duplicate so that should not b repeat.
Maya Edward Try this one.. just change the cell reference on this formula to whatever the cell reference of ur data.
Name (drag the formula down): =IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,MATCH(ROW($A$2:$A$8)-ROW($A$1),MATCH($A$2:$A$8,$A$2:$A$8,0),0),ROWS(D$2:D2))),"")
Categories (drag it to the right then down: =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($B$2:$B$8)-ROW($B$1))/((MATCH($A$2:$A$8&$B$2:$B$8,$A$2:$A$8&$B$2:$B$8,0)=(ROW($B$2:$B$8)-ROW($B$1)))*($A$2:$A$8=$D2)),COLUMNS($E2:E2))),"")
If u have Excel 365.. =LET(name,A2:A8,cat,B2:B8,uniq,UNIQUE(name),row,ROWS(uniq),col,ROWS(name), tjoin,BYROW(UNIQUE(IF(uniq=TRANSPOSE(name),TRANSPOSE(cat),""),1),LAMBDA(x,TEXTJOIN("",,x))), xpnd,IFNA(EXPAND(tjoin,,col),tjoin),HSTACK(uniq,MID(xpnd,MOD(SEQUENCE(row,col)-1,col)+1,1)))
