Combining cells with the same data but keeping some data separate

[jarrodexcel]

Good afternoon all.

Have a tricky goal to try and combine two rows with the same data, so merge it into one row of data. I will need a formula that can combine them as ill have lots of this data coming in.

As you can see below - A, B and C all share the same data and i need them merged into one, but D has different data and would need that to multiply by each other so the 5xABC etc and 3x ABC becomes 8xABC1111-1999.

Any help will go a long way !

Thank you so much


A B C D

John Doe	125522​	The big tree hotel, abc london	5xABC1111-1999
John Doe	125522​	The big tree hotel, abc london	3xABC1111-1999

 

[Legacy 143009]

What happens if:

John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
John Doe	125522	The big tree hotel, abc london	3xABC1111-1999

 

[jarrodexcel]

What happens if:

John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
John Doe	125522	The big tree hotel, abc london	3xABC1111-1999

That is another issue that i was going to raise after ! but in some cases it will be 5xDEF and 3xABC etc, so that will complicate the formula alot

 

[JvdV]

I'll give it a shot based on the question raised by @Flashbond :

Book1
	A	B	C	D
1	John Doe	125522	The big tree hotel, abc london	5xABC1111-1999
2	John Doe	125522	The big tree hotel, abc london	3xABC1111-1999
3	John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
4
5
6	John Doe	125522	The big tree hotel, abc london	8xABC1111-1999
7	John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
Sheet5

Cell Formulas
Range	Formula
A6:D7	A6	=LET(x,UNIQUE(HSTACK(A1:C3,TEXTAFTER(D1:D3,"x"))),HSTACK(TAKE(x,,3),BYROW(x,LAMBDA(y,SUM(--TEXTBEFORE(FILTER(D1:D3,MMULT(--(A1:C3=TAKE(y,,3)),{1;1;1})=3),"x"))))&"x"&DROP(x,,3)))
Dynamic array formulas.

 

[jarrodexcel]

I'll give it a shot based on the question raised by @Flashbond :

Book1
	A	B	C	D
1	John Doe	125522	The big tree hotel, abc london	5xABC1111-1999
2	John Doe	125522	The big tree hotel, abc london	3xABC1111-1999
3	John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
4
5
6	John Doe	125522	The big tree hotel, abc london	8xABC1111-1999
7	John Doe	125522	The big tree hotel, abc london	5xDEF1111-1999
Sheet5

Cell Formulas
Range	Formula
A6:D7	A6	=LET(x,UNIQUE(HSTACK(A1:C3,TEXTAFTER(D1:D3,"x"))),HSTACK(TAKE(x,,3),BYROW(x,LAMBDA(y,SUM(--TEXTBEFORE(FILTER(D1:D3,MMULT(--(A1:C3=TAKE(y,,3)),{1;1;1})=3),"x"))))&"x"&DROP(x,,3)))
Dynamic array formulas.

Thank you for this ! Partially worked but this is my fault for not explaining fully, but if in cell C the data is the same i need those to all combine and then make the data in cell D combine as well, or become a list like 2xABC123,3xDEF123 etc.

I will post the full TABLE below so you can see, For example in C the ARKS will need to combine but their D data values will need to become a list in the same cell. The big tree hotel will need to combine as well, but as the D cell values both share the same ABC format they can become 8x.

I hope this makes better sense ?

A B C D

John DOE	1255887​	The office stop	2xABC1234-40004
John DOE	1234466​	Two tree style shop	2xABC1111-4004
John DOE	1255442​	Viewing Galary	5x1155-1234tt
John DOE	1245222​	The Ark	3xABC1234-9999
John DOE	1245222​	The Ark	2x1234-9999_SS
John DOE	5222369​	Park Plaza	7xABC1235-1994
John DOE	5478116​	Orange floor shop	3xC111_2_4-SS
John DOE	5544899​	The top stop	1xABC4004-1234
John DOE	1234455​	The big tree hotel, abc london	6xABC1155-9999
John DOE	1234455​	The big tree hotel, abc london	2xABC1155-9999

 

[JvdV]

So something along these lines then?

Book1
	A	B	C	D
1	John DOE	1255887	The office stop	2xABC1234-40004
2	John DOE	1234466	Two tree style shop	2xABC1111-4004
3	John DOE	1255442	Viewing Galary	5x1155-1234tt
4	John DOE	1245222	The Ark	3xABC1234-9999
5	John DOE	1245222	The Ark	2x1234-9999_SS
6	John DOE	5222369	Park Plaza	7xABC1235-1994
7	John DOE	5478116	Orange floor shop	3xC111_2_4-SS
8	John DOE	5544899	The top stop	1xABC4004-1234
9	John DOE	1234455	The big tree hotel, abc london	6xABC1155-9999
10	John DOE	1234455	The big tree hotel, abc london	2xABC1155-9999
11
12	John DOE	1255887	The office stop	2xABC1234-40004
13	John DOE	1234466	Two tree style shop	2xABC1111-4004
14	John DOE	1255442	Viewing Galary	5x1155-1234tt
15	John DOE	1245222	The Ark	3xABC1234-9999,2x1234-9999_SS
16	John DOE	5222369	Park Plaza	7xABC1235-1994
17	John DOE	5478116	Orange floor shop	3xC111_2_4-SS
18	John DOE	5544899	The top stop	1xABC4004-1234
19	John DOE	1234455	The big tree hotel, abc london	8xABC1155-9999
Sheet5

Cell Formulas
Range	Formula
A12:D19	A12	=LET(x,UNIQUE(A1:C10),HSTACK(x,BYROW(x,LAMBDA(y,LET(z,FILTER(D1:D10,MMULT(--(A1:C10=y),{1;1;1})=3),q,TEXTAFTER(z,"x"),TEXTJOIN(",",,MAP(UNIQUE(q),LAMBDA(r,SUM(--FILTER(TEXTBEFORE(z,"x"),q=r))&"x"&r))))))))
Dynamic array formulas.

 

[jarrodexcel]

So something along these lines then?

Book1
	A	B	C	D
1	John DOE	1255887	The office stop	2xABC1234-40004
2	John DOE	1234466	Two tree style shop	2xABC1111-4004
3	John DOE	1255442	Viewing Galary	5x1155-1234tt
4	John DOE	1245222	The Ark	3xABC1234-9999
5	John DOE	1245222	The Ark	2x1234-9999_SS
6	John DOE	5222369	Park Plaza	7xABC1235-1994
7	John DOE	5478116	Orange floor shop	3xC111_2_4-SS
8	John DOE	5544899	The top stop	1xABC4004-1234
9	John DOE	1234455	The big tree hotel, abc london	6xABC1155-9999
10	John DOE	1234455	The big tree hotel, abc london	2xABC1155-9999
11
12	John DOE	1255887	The office stop	2xABC1234-40004
13	John DOE	1234466	Two tree style shop	2xABC1111-4004
14	John DOE	1255442	Viewing Galary	5x1155-1234tt
15	John DOE	1245222	The Ark	3xABC1234-9999,2x1234-9999_SS
16	John DOE	5222369	Park Plaza	7xABC1235-1994
17	John DOE	5478116	Orange floor shop	3xC111_2_4-SS
18	John DOE	5544899	The top stop	1xABC4004-1234
19	John DOE	1234455	The big tree hotel, abc london	8xABC1155-9999
Sheet5

Cell Formulas
Range	Formula
A12:D19	A12	=LET(x,UNIQUE(A1:C10),HSTACK(x,BYROW(x,LAMBDA(y,LET(z,FILTER(D1:D10,MMULT(--(A1:C10=y),{1;1;1})=3),q,TEXTAFTER(z,"x"),TEXTJOIN(",",,MAP(UNIQUE(q),LAMBDA(r,SUM(--FILTER(TEXTBEFORE(z,"x"),q=r))&"x"&r))))))))
Dynamic array formulas.

JvdV ! You are a hero ! i have just run it on excel and so far it seems to be working well. I am going to test it more over the next few days with more data coming in. But i think you might have just saved me hours of work a day !!!! You are such a legend ! Much LOVE <3

 

[JvdV]

Glad to hear it worked and saves time as well @jarrodexcel

 

[jarrodexcel]

Glad to hear it worked and saves time as well @jarrodexcel

Did have a quick question, if i had an extra cell that i needed to add in, the cell only contains additional information for the account and is not related to the 1xabc etc, how can i impliment that into the formula ?

 

[JvdV]

I think that does need some visual clarification @jarrodexcel