I've got almost 70K lines of exported non table friendly data to sort out

[hardend]

Hi!

So we needed to export about 750 customers data into another system. About 67k rows in total
There are about 9 columns and 60-90 rows for each customer, depending on how much data we have for each customer.

Customer number
835	Company A		...
	Some road		...	phone	035-123456
	some town		...	phone 2	070-354786
			...	Fax:	020-159632
...	...	...	...	...	...
	Referens:	sam smith	...
	VAT-nr:	3265-6363	...	Alternative:	Data 1
...	...	...	...	...	...
	Transporter:	DHL	...
...	...	...	...	...	...
256	Some other Company		...
...	...	...	...	...	...
512	Company B		...

The only data i actually needed to extract for each customer is the Customer number, Company name, Transporter and Alternative

I've been messing around with vba crashing excel and failing to excecute as I want, for a couple of days now trying to get the data into a table looking like this:

835	Company A	DHL	Data 1
256	Some other Company	DB Schenker	Data 2
512	Company B	DHL	Data 3

Since I'm not sure what to search for google is not helping much.

How would you approach it?

 

[Toadstool]

Hi Hardend,

I note your data doesn't support the example solution but I guess that's just an omission.

I would add a column (A) to repeat the Customer Number so I had a lookup for the Transporter and Alternative, then I'd build the result table using AGGREGATE (columns I to L).

Hardend.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer number	Customer number							Customer number	Company Name	Transporter	Alternative
2	835	835	Company A		...				835	Company A	DHL	Data 1
3	835		Some road		...	phone	035-123456		256	Some other Company
4	835		some town		...	phone 2	070-354786		512	Company B
5	835				...	Fax:	020-159632
6	835	...	...	...	...	...	...
7	835		Referens:	sam smith	...
8	835		VAT-nr:	3265-6363	...	Alternative:	Data 1
9	835	...	...	...	...	...	...
10	835		Transporter:	DHL	...
11	835	...	...	...	...	...	...
12	256	256	Some other Company		...
13	256	...	...	...	...	...	...
14	512	512	Company B		...
Sheet1

Cell Formulas
Range	Formula
I2:I14	I2	=IFERROR(INDEX($A$2:$A$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
J2:J14	J2	=IFERROR(INDEX($C$2:$C$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
K2:K14	K2	=IFERROR(INDEX($D$2:$D$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($C$2:$C$99999="Transporter:")),ROW()-ROW($I$1))),"")
L2:L14	L2	=IFERROR(INDEX($G$2:$G$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($F$2:$F$99999="Alternative:")),ROW()-ROW($I$1))),"")
A2:A14	A2	=IF(ISNUMBER(B2),B2,N(A1))

 

[hardend]

Hi Hardend,

I note your data doesn't support the example solution but I guess that's just an omission.

I would add a column (A) to repeat the Customer Number so I had a lookup for the Transporter and Alternative, then I'd build the result table using AGGREGATE (columns I to L).

Hardend.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer number	Customer number							Customer number	Company Name	Transporter	Alternative
2	835	835	Company A		...				835	Company A	DHL	Data 1
3	835		Some road		...	phone	035-123456		256	Some other Company
4	835		some town		...	phone 2	070-354786		512	Company B
5	835				...	Fax:	020-159632
6	835	...	...	...	...	...	...
7	835		Referens:	sam smith	...
8	835		VAT-nr:	3265-6363	...	Alternative:	Data 1
9	835	...	...	...	...	...	...
10	835		Transporter:	DHL	...
11	835	...	...	...	...	...	...
12	256	256	Some other Company		...
13	256	...	...	...	...	...	...
14	512	512	Company B		...
Sheet1

Cell Formulas
Range	Formula
I2:I14	I2	=IFERROR(INDEX($A$2:$A$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
J2:J14	J2	=IFERROR(INDEX($C$2:$C$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
K2:K14	K2	=IFERROR(INDEX($D$2:$D$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($C$2:$C$99999="Transporter:")),ROW()-ROW($I$1))),"")
L2:L14	L2	=IFERROR(INDEX($G$2:$G$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($F$2:$F$99999="Alternative:")),ROW()-ROW($I$1))),"")
A2:A14	A2	=IF(ISNUMBER(B2),B2,N(A1))

Thanks Toadstool for the quick reply!

Now my table looks just as yours.
I'm really impressed by your skills!

But I'm not able to get the formula to work in K3:K9999 and L3:L9999
It works for K2 and L2 tough.

In not familiar with the aggregate function at all and have been the googleing without anything to show for it.
Do you have any idea why it doesn't continue?

 

[Toadstool]

My mistake. I only need the 1st match of Customer Number and Transporter:/Alternative: so change the K2 & L2 down formulae as below.

Hardend.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer number	Customer number							Customer number	Company Name	Transporter	Alternative
2	835	835	Company A		...				835	Company A	DHL	Data 1
3	835		Some road		...	phone	035-123456		256	Some other Company	DB Schenker	Data 2
4	835		some town		...	phone 2	070-354786		512	Company B	DHL	Data 3
5	835				...	Fax:	020-159632
6	835	...	...	...	...	...	...
7	835		Referens:	sam smith	...
8	835		VAT-nr:	3265-6363	...	Alternative:	Data 1
9	835	...	...	...	...	...	...
10	835		Transporter:	DHL	...
11	835	...	...	...	...	...	...
12	256	256	Some other Company		...
13	256	...	Transporter:	DB Schenker	...	Alternative:	Data 2
14	512	512	Company B		...
15	512		…
16	512		Transporter:	DHL
17	512					Alternative:	Data 3
Sheet1

Cell Formulas
Range	Formula
I2:I14	I2	=IFERROR(INDEX($A$2:$A$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
J2:J14	J2	=IFERROR(INDEX($C$2:$C$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
K2:K14	K2	=IFERROR(INDEX($D$2:$D$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($C$2:$C$99999="Transporter:")),1)),"")
L2:L14	L2	=IFERROR(INDEX($G$2:$G$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($F$2:$F$99999="Alternative:")),1)),"")
A2:A17	A2	=IF(ISNUMBER(B2),B2,N(A1))

 

[hardend]

My mistake. I only need the 1st match of Customer Number and Transporter:/Alternative: so change the K2 & L2 down formulae as below.

Hardend.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer number	Customer number							Customer number	Company Name	Transporter	Alternative
2	835	835	Company A		...				835	Company A	DHL	Data 1
3	835		Some road		...	phone	035-123456		256	Some other Company	DB Schenker	Data 2
4	835		some town		...	phone 2	070-354786		512	Company B	DHL	Data 3
5	835				...	Fax:	020-159632
6	835	...	...	...	...	...	...
7	835		Referens:	sam smith	...
8	835		VAT-nr:	3265-6363	...	Alternative:	Data 1
9	835	...	...	...	...	...	...
10	835		Transporter:	DHL	...
11	835	...	...	...	...	...	...
12	256	256	Some other Company		...
13	256	...	Transporter:	DB Schenker	...	Alternative:	Data 2
14	512	512	Company B		...
15	512		…
16	512		Transporter:	DHL
17	512					Alternative:	Data 3
Sheet1

Cell Formulas
Range	Formula
I2:I14	I2	=IFERROR(INDEX($A$2:$A$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
J2:J14	J2	=IFERROR(INDEX($C$2:$C$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(ISNUMBER($B$2:$B$99999)),ROW()-ROW($I$1))),"")
K2:K14	K2	=IFERROR(INDEX($D$2:$D$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($C$2:$C$99999="Transporter:")),1)),"")
L2:L14	L2	=IFERROR(INDEX($G$2:$G$99999,AGGREGATE(15,6,ROW($B$2:$B$99999)-ROW($B$1)/(($A$2:$A$99999=$I2)*($F$2:$F$99999="Alternative:")),1)),"")
A2:A17	A2	=IF(ISNUMBER(B2),B2,N(A1))

You are wonderful Toadstool!!!!

That did it!
Thank you so much!!!

 

[Toadstool]

You're welcome.

Quick explanation...

If you type =AGGREGATE( you'll see a whole bunch of options. I'm using 15 for the SMALL function. I'm then using 6 to tell it to ignore internal errors. Normally the array will be a range of cells and it will give me the kth SMALLest value from that list, but I'm using ROW numbers (and then subtracting whatever the header row is to get the correct offset). This means it will go through rows 2 to 99999.
INTERESTING NOTE: It just uses the row numbers so instead of ROW($B$2:$B$99999)-ROW($B$1) I could use ROW($zz$2:$zz$99999)-ROW($zz$1) and it would still work fine.

As AGGREGATE goes through each ROW number I divide it by (ISNUMBER($B$2:$B$99999)). If it is not a number then I get a logical zero (FALSE) and dividing that into the row number gives #DIV/0 which the 6 option tells it to ignore. If it IS a NUMBER I get logical 1 (TRUE) which divided into that row gives the row number, which INDEX then uses to return the Customer Number.

Next is the k parameter ROW()-ROW($I$1) so that adds to the SMALL function search so I2 is looking for the 1st numeric in column B but I3 will look for the 2nd numeric, and return 256.

The Company Name is basically the same but for Transporter and Alternative I only want to find the first entry where the Company Number in the new column (so always contains the current/last Company Number in column A) matches that in column I and Transporter: is in column C, Alternative: is in column F.