COMBINE ROWS INTO SINGLE COLUMN

[spectraflame]

I just need some suggestions on how to better organize the data. My output has multiple rows with the same information in all cells except for one which denotes the service type. As an example there are 3 rows of data with duplicate information however in column there is a 1, 2 or 3 to denote the service type. How can that information be combined into a single row for the account information but have the 1, 2, or 3 listed in a column for a single record.

So if I had 3 rows all with the same data except for 1, 2, or 3 in column E, the end result would give me a single row with all of the information from the first row, but have 2 and/or 3 in other columns of the same row?

There is a possibility of having only a single row with a 1, 2 or 3 meaning that customer only has a specific service type.

There is a possibility of having a duplicate row of 1, 2 or 3 which means that customer has multiple service types of that kind.

 

[DanteAmor]

You could put a sample with generic data, use XL2BB tool, show what you have and the expected result.

 

[spectraflame]

Top is raw output. Bottom is the desired result or something close to this.

Acct Connect Dt	Account	Customer	Rev Class	Provider	Name	Service Address
1/1/1995	1001	1	COMM	1	JOHN	123 MAIN
1/1/1995	1001	1	COMM	1	JOHN	123 MAIN
1/1/1995	1001	1	COMM	3	JOHN	123 MAIN
1/1/1995	1001	1	COMM	2	JOHN	123 MAIN
1/1/1995	7001	7	COMM	2	ROBERT	125 MAIN
1/1/1995	7001	7	COMM	3	ROBERT	125 MAIN
1/1/1995	52001	52	COMM	3	JIMMIE	127 MAIN
1/1/1995	52001	52	COMM	2	JIMMIE	127 MAIN
Acct Connect Dt	Account	Customer	Rev Class	Provider	Name	Service Address	Provider2	Provider3
1/1/1995	1001	1	COMM	1	JOHN	123 MAIN	2	3
1/1/1995	7001	7	COMM	2	ROBERT	125 MAIN		3
1/1/1995	52001	52	COMM	3	JIMMIE	127 MAIN	2

 

[DanteAmor]

Dante Amor
	E	F	G
1	Provider	Name	Service Address
2	1	JOHN	123 MAIN
3	1	JOHN	123 MAIN
4	3	JOHN	123 MAIN
5	2	JOHN	123 MAIN
Hoja2

Is it always the same name and Address? if it is different what would be the result?

Dante Amor
	B	C
1	Account	Customer
2	1001	1
3	1001	1
4	1001	1
5	1001	1
Hoja2

Is the Account - Customer relationship always the same?

Dante Amor
	E	F	G	H	I
39	Provider	Name	Service Address	Provider2	Provider3
40	2	ROBERT	125 MAIN		3
Hoja2

How to know that the number 3 goes in the "Provider3" column, it seems very obvious, but I suppose that in reality you don't have a 3 in the register nor a 3 in the header.

Lastly, do you have a maximum of 3 providers?

 

[spectraflame]

The address and account number will always be the same for service related to the same account. There may only be an entry with a single 1, 2, or 3, or a combination of the 3 or there could be duplicates such as having two entries with a (1), a single entry with a (2), and a single entry with a (3).

 

[CA_Punit]

Is this what you want

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	Helper Column	Acct Connect Dt	Account	Customer	Rev Class	Provider	Name	Service Address
3	1	01/01/1995	1001	1	COMM	1	JOHN	123 MAIN
4	2	01/01/1995	1001	1	COMM	1	JOHN	123 MAIN
5	3	01/01/1995	1001	1	COMM	3	JOHN	123 MAIN
6	4	01/01/1995	1001	1	COMM	2	JOHN	123 MAIN
7	1	01/01/1995	7001	7	COMM	2	ROBERT	125 MAIN
8	2	01/01/1995	7001	7	COMM	3	ROBERT	125 MAIN
9	1	01/01/1995	52001	52	COMM	3	JIMMIE	127 MAIN
10	2	01/01/1995	52001	52	COMM	2	JIMMIE	127 MAIN
11
12									PROVIDER
13		Acct Connect Dt	Account	Customer	Rev Class	Provider	Name	Service Address	2	3
14		01/01/1995	1001	1	COMM	1	JOHN	123 MAIN	2	3
15		01/01/1995	7001	7	COMM	2	ROBERT	125 MAIN		3
16		01/01/1995	52001	52	COMM	3	JIMMIE	127 MAIN	2
17
18
Sheet1

Cell Formulas
Range	Formula
A3:A10	A3	=COUNTIF($G$3:$G3,G3)
B14:H16	B14	=INDEX(B$3:B$10,AGGREGATE(15,6,1/($A$3:$A$10=1)*(ROW($A$3:$A$10)-ROW($A$3)+1),(ROW($A3:$A3)-ROW($A$3)+1)))
I14:I16,J15:J16	I14	=IF(MAX(IF(IF($G$3:$G$10=$G14,$F$3:$F$10)=I$13,I$13,""))=$F14,"",MAX(IF(IF($G$3:$G$10=$G14,$F$3:$F$10)=I$13,I$13,"")))
J14	J14	=IF(MAX(IF(IF($G$3:$G$10=$G14,$F$3:$F$10)=J$13,J$13,""))=$F$14,"",MAX(IF(IF($G$3:$G$10=$G14,$F$3:$F$10)=J$13,J$13,"")))