Convert "multi line" data to "single line" database

[rhobert]

Hello all,

I have received an Excel file that is structured as follows.

customer 1 ID	customer 1 name	customer 1 email	customer 1 website
	customer 1 address	customer 1 post code
customer 2 ID	customer 2 name	customer 2 email	customer 2 website
	customer 2 address	customer 2 post code

(there are more columns, this is to give you an idea). There are around a 1,000 customers and each customer has 4 lines. Some cells have been merged.

I need to convert this to a single line database: all data for each customer needs to be on a single line.

How to achieve this (easily)?

regards,
rhobert

 

[kweaver]

You say each customer (I assume customer ID) has 4 lines, yet you show 2 for each.
Would you provide some samples using XL2BB?

 

[kweaver]

If you don't want to use VBA: with 2 lines like your initial example, you might try modifying the following.
Then select all, copy, paste special values.
Then filter and select only the blanks (from Column A) and delete those rows. Then, un-filter.
If there are more than 2 lines associated with each ID, you could adapt this to use MOD.

FileFolderFileDetails.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Field 1	Field 2	Field 3	Field 4	Field 5
2	123	AAA	BBB	CCC	DDD	EEE	FFF	GGG	HHH	JJJ
3		FFF	GGG	HHH	JJJ
4	456	XXXX	YYYY	ZZZZ	AAAA	BBBB	QQQ	RRR	SSS	TTT
5		QQQ	RRR	SSS	TTT
Sheet6

Cell Formulas
Range	Formula
G2:J5	G2	=IF(ISEVEN(ROW()),B3,"")

 

[rhobert]

	Code	Naam	Zoekcode	Telefoon	Klant	Bron
		Adres		Contactpersoon	Fax	Leverancier
		Postcode	Plaats	Bankrekening	E-mail	Btw-nummer
		Land		Website
	1000914	Company 1			V
		Hofplein 2001		Financiële administratie
		3032 LK	Rotterdam		person@domain.com
		Nederland
	1000906	Super Hospital			V
		Prins Emptystraat 99		D. Superwoman
		3576ZZ	Capelle aan den IJssel		abc@def.nl
		Nederland

This is a screenshot, just to be sure

 

[kweaver]

Try this or something close; then copy, paste special values; then filter on blanks in column A, and delete those; then remove the filter.

FileFolderFileDetails.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	Naam	Zoekcode	Telefoon	Klant	Bron	Adres		Contactpersoon	FAX	Leverancier	Postcode	Plaats	Bankrekening	E-Mail	Btw-nummer	Land
2	1000914	Company 1			V		Hofplein 2001		Financiële administratie			3032 LK	Rotterdam		person@domain.com		Nederland
3		Hofplein 2001		Financiële administratie
4		3032 LK	Rotterdam		person@domain.com
5		Nederland
6	1000906	Super Hospital			V		Prins Emptystraat 99		D. Superwoman			3576ZZ	Capelle aan den IJssel		abc@def.nl		Nederland
7		Prins Emptystraat 99		D. Superwoman
8		3576ZZ	Capelle aan den IJssel		abc@def.nl
Sheet6

Cell Formulas
Range	Formula
G2:K2,G3:J8	G2	=IF(2=MOD(ROW(),4),IF(ISBLANK(B3),"",B3),"")
L2:P2,L3:O8	L2	=IF(2=MOD(ROW(),4),IF(ISBLANK(B4),"",B4),"")
Q2:Q8	Q2	=IF(2=MOD(ROW(),4),IF(ISBLANK(B5),"",B5),"")

 

[rhobert]

Works like a charm, those MOD formulas. Thank you!

 

[kweaver]

Great. Glad it worked for you. Thanks for the confirmation.