Trying to move columns into rows without success

[Adrian_Cornwall]

Good Afternoon

I have been trying to come up with a solution for the following excel problem.

I have extracted an access database and the result appears as :-

keyno pi_sort reg ntu c/n actype series current
36290 1 N9658N 38-81A0174 PA-38 112 Tomahawk N107LH
36290 2 PK-ATG 38-81A0174 PA-38 112 Tomahawk N107LH
36290 3 N107LH 38-81A0174 PA-38 112 Tomahawk N107LH

I need to arrange this data as below :-

36290 N9658N PK-ATG N107LH

ie in rows, not in columns

I realise that you can do copy, then paste to row, but I have 750,000 rows !!

I would really appreciate any assistance

Best Regards

Adrian

[Fluff13]

Hi & welcome to the board.
If you have dynamic arrays how about
In L7
=UNIQUE(FILTER(C7:C100,C7:C100<>""))
In N7 filled down
=TRANSPOSE(FILTER(E$7:E$100,(C$7:C$100=L7)*(L7<>""),""))

[Adrian_Cornwall]

Many thanks for the swift reply,

When i used those formula's excel says " That function isnt valid "

I am using Excel from office 365

Sorry to be a pain...

Regards

Adrian

[Fluff13]

Ok, you must be on the semi annual channel, in which case you are probably better off with a macro.
Is that OK? If so does your data normally start in A1 rather than C6?

[Adrian_Cornwall]

Hello again

Yes the first cell is A1

Regards

[Fluff13]

Ok, give this a go

Please Login or Register  to view this content.

[Adrian_Cornwall]

You are an absolute genius !!!!!!!!

That has probably saved me about three weeks work.......

Many Many Thanks

Best Regards

Adrian

[Fluff13]

You're welcome & thanks for the feedback.

[alansidman]

An alternative solution is to use Power Query/Get & Transform. Here is the Mcode and the file you can review.

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	keyno	1	2	3
2	36289	N91334
3	36290	N9658N	PK-ATG	N107LH
4	36291	N9654N	PK-ATH	N108LH
5	36292	N9655N	PK-ATI	N109LH

Sheet: Sheet2

[Adrian_Cornwall]

Hi Alan,

Brilliant, that works just as well !!

Amazing how you spend weeks, copying & pasting text, when it could be done in just a matter of minutes !!

Could I be really cheeky, I have just hit another problem !! ( i will also create another thread for this problem )

I have a spreadsheet with two sheets in it, one sheet called master, and the other sheet called MFR Model Code

in the master sheet there is a column called "MFR MDL CODE", which contains a code to identify the aircraft model

in the MFR model code sheet is the decode, ie 1384856 = BOEING 747-206B

what i need to do is replace the contents of MFR Model Code column in master, with the actual aircraft type contained in the MFR model code sheet (see attached)

Best Regards

Adrian

[alansidman]

In C5 =VLOOKUP(B5,'MFR MODEL CODE'!$A$2:$B$12,2,0) and copy down.