Excel column to rows

[david gabra]

Hi
how to convert the excel form column to all data on the same row

Hi

Process	User	Geographical Reason
OCR	r	BACKL
OCR	r	GLOBAL
OCR	r	BACKL
OCR	v	GLOBAL
OCR	v	IN

i want to see 2 rows and the data in different columns on the same row

so the table above become the below.

r backl global backl
v gloval in

I have no idea - i tried all - or maybe it just too late.

any help would be very much appreciated.

David.

 

[Nebu]

Hi:

May be this? Using power query.

Thanks

 

[Peter Bartholomew]

Producing a list of lists is one of those things that is annoyingly difficult. A pivot table will produce a count of items but not a list.
It is reasonably possible with dynamic arrays but painful with traditional Excel.

The logical steps are to single out the unique combinations of Process and User.
With dynamic arrays this is
= UNIQUE(ProcessUser)
where ProcessUser is an array comprising the first two columns.
With standard Excel you could take a copy and remove duplicates.

Then, for each row of the output table you need to filter the geographical data in the original table and transpose to obtain a row
= TRANSPOSE( FILTER(Geog, (ProcessList=@Process) * (UserList=@User) ) )
With traditional Excel it is a case of returning record numbers for the matches, using SMALL to compact them, lookup the geographical data by index or by using LOOKUP and finally transpose. If you manage that, you will have earnt yourself a beer or two!

 

[david gabra]

Producing a list of lists is one of those things that is annoyingly difficult. A pivot table will produce a count of items but not a list.
It is reasonably possible with dynamic arrays but painful with traditional Excel.

The logical steps are to single out the unique combinations of Process and User.
With dynamic arrays this is
= UNIQUE(ProcessUser)
where ProcessUser is an array comprising the first two columns.
With standard Excel you could take a copy and remove duplicates.

Then, for each row of the output table you need to filter the geographical data in the original table and transpose to obtain a row
= TRANSPOSE( FILTER(Geog, (ProcessList=@Process) * (UserList=@User) ) )
With traditional Excel it is a case of returning record numbers for the matches, using SMALL to compact them, lookup the geographical data by index or by using LOOKUP and finally transpose. If you manage that, you will have earnt yourself a beer or two!

View attachment 59263

thank you. I will look into that. Pretty complicated.

 

[david gabra]

Hi:

May be this? Using power query.

Thanks

I just downloaded power query - not sure what you did - which is perfect.
Can you help.

 

[Nebu]

Hi:

What version of excel you are using? I am using excel 2016, but power query is available from excel 2013 onward as a native function, I guess in earlier version you will have add-in power query to get it work. Power query is a powerful tool for data transformation and will get rid of complex formulas that can stuff up excel spread sheet if you are handling large amounts of data.The solution I have given you will work for any amount of data you add to your table by one click on "Refresh All" in data tab of excel.

The following link will get you started with basics of power query

https://support.office.com/en-us/ar...er-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

Thanks

 

[AlanSidman]

My original go to for PQ was "M is for (Data) Monkey" a book by Ken Puls and Miguel Escobar.

 

[david gabra]

Hi:

What version of excel you are using? I am using excel 2016, but power query is available from excel 2013 onward as a native function, I guess in earlier version you will have add-in power query to get it work. Power query is a powerful tool for data transformation and will get rid of complex formulas that can stuff up excel spread sheet if you are handling large amounts of data.The solution I have given you will work for any amount of data you add to your table by one click on "Refresh All" in data tab of excel.

The following link will get you started with basics of power query

https://support.office.com/en-us/ar...er-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

Thanks

thank you