for display only
Big Blue Interactive The Corner Forum  
Back to the Corner

Archived Thread

NFT: Excel - Transpose question

Matt M. : 1/24/2023 12:29 pm
Last June I had a question about transposing a dataset in Excel and linked it below.

The crux is that I have Row 1 (A1:R1) with header data
A1 = BLANK; B1 = 16; C1 = 15; ... R1 = 0

Column A contains the "header" data for each row:
A1 = BLANK
A2 = 85
A3 = 84
A4 = 83
.
.
.
A87 = 0

I need the data for both the rows and columns to be ASCENDING, Result should be:

A1 = BLANK; B1 = 0; C1 = 1; ... R1 = 16
A2 = 0
A3 = 1
.
.
.
A87 = 85

Sheet1 = Raw data pasted in

The resulting formula that worked last year is:
=transpose(query(arrayformula(value(query(transpose(query({Sheet1!A:EC},"Select * where Col2 is not null Order By Col1 asc",1)),"Select * where Col2 is not null Order by Col1 asc",1))),"Select * where Col2 is not null Order by Col1 asc",1))

When I paste this in Sheet2 in my current document, Excel is not executing this as a formula.

Any thoughts, other than changing sort criteria instead?
Archived Excel Thread - ( New Window )
I should add  
Matt M. : 1/24/2023 12:31 pm : link
I need both the Headers and the resulting data to transpose to ASCENDING.

I am NOT transposing the Columns with the Rows.
Update  
Matt M. : 1/24/2023 1:05 pm : link
In re-reading the archived thread, the solution I included was for Google Sheets specifically, not to be used directly in Excel.

That does work.

Is there a similar solution for Excel directly?
Matt, I did that in Google Sheets for you, not excel.  
robbieballs2003 : 1/24/2023 2:11 pm : link
Excel doesn't have the query function.
I am sure there is a solution in Excel but I'd have to do it  
robbieballs2003 : 1/24/2023 2:13 pm : link
to figure it out and I don't have time to do that.
I'm sure there's a really quirky formula in Excel  
Gmen703 : 1/24/2023 2:18 pm : link
that can get you what you need. It's just the query function in Google sheets is better at handling "database-like" things in a formulaic manner (ducks from the power query and VBA ADODB users).


I know. Not much help. Is the Google solution not adequate?
Sounds like you want to sort columns and rows  
BobR in Durham : 1/24/2023 2:26 pm : link
according to header values rather than transpose the table. Is that correct? If so, is this a one-time manipulation or do you want code to repeat it?
RE: I'm sure there's a really quirky formula in Excel  
Matt M. : 1/24/2023 2:35 pm : link
In comment 16012261 Gmen703 said:
Quote:
that can get you what you need. It's just the query function in Google sheets is better at handling "database-like" things in a formulaic manner (ducks from the power query and VBA ADODB users).


I know. Not much help. Is the Google solution not adequate?
Google sheets does work
RE: Sounds like you want to sort columns and rows  
Matt M. : 1/24/2023 2:38 pm : link
In comment 16012276 BobR in Durham said:
Quote:
according to header values rather than transpose the table. Is that correct? If so, is this a one-time manipulation or do you want code to repeat it?
More or less, that is accurate. I initially did a multi-step process to sort the data. But, I have to do this a few times a year for this file and I have no control in terms of the source of the file. I would prefer a formula that does it in one shot. For now, the solution for Google Sheets works, but if you know how to do it directly in Excel, that would be awesome.
RE: Matt, I did that in Google Sheets for you, not excel.  
Matt M. : 1/24/2023 2:38 pm : link
In comment 16012247 robbieballs2003 said:
Quote:
Excel doesn't have the query function.
Yup - if you see my post directly above yours, I realized it was in Google and realize why.
My VBA skills are rudimentary at best  
BobR in Durham : 1/24/2023 3:11 pm : link
but I's create a macro in excel to sort according to your header row and columns. This is a poor example but it works:

Sub Macro3()
'
' Macro3 Macro
'

'
Cells.Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add2 Key:=Range("A2:A100") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:ZZ100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B1:ZZ1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B1:ZZ100")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub


Not sure how many rows and columns your tables consist of, but you can modify the cell parameters to suit. This code is for a table from A1:ZZ100. Sorry if the indentations don't come through.
I may not understand the problem here, but...  
D HOS : 1/24/2023 3:42 pm : link
Looking at the earlier thread, you seem to have the solution. Just sort columns, then sort rows. I tried it on your test data and it seemed to work fine.

When you sort, click the "my data has headers" checkbox. You shouldn't have to move the header row / column back to the top/left. It should stay in both cases because of the "blank" upper left corner. That will come first when sorting ascending, like you are needing.

You can create a macro for this.

If the data is variably sized each time, then it is a little more tricky, but you should be able to use ctrl-A when recording your macro. However if that translates into a literal range so that the next time you run the macro it wants to use the same literal range, instead of ctrl-A again, then add VBA code to your macro, to determine, and name, the range of used cells - for sorting. This is not too tough to find through Googling.
RE: I may not understand the problem here, but...  
Matt M. : 1/24/2023 4:29 pm : link
In comment 16012486 D HOS said:
Quote:
Looking at the earlier thread, you seem to have the solution. Just sort columns, then sort rows. I tried it on your test data and it seemed to work fine.

When you sort, click the "my data has headers" checkbox. You shouldn't have to move the header row / column back to the top/left. It should stay in both cases because of the "blank" upper left corner. That will come first when sorting ascending, like you are needing.

You can create a macro for this.

If the data is variably sized each time, then it is a little more tricky, but you should be able to use ctrl-A when recording your macro. However if that translates into a literal range so that the next time you run the macro it wants to use the same literal range, instead of ctrl-A again, then add VBA code to your macro, to determine, and name, the range of used cells - for sorting. This is not too tough to find through Googling.
But, the header row also needs to be re-sorted, which is an extra step, or something that needs to be accounted for.
Sort the headers and rows...hmmm  
Gmen703 : 1/24/2023 10:37 pm : link
But I would go the VBA route.

Depending on the size of the data (# of cols/rows), I'd throw it into a ListObject (Insert-->table; assuming the headers are unique), create a VBA sort for my ListColumns by name, and then sort the rows. Then, if you need it as a range, convert it back to a range.

I'm guessing the "BLANK" would get re-labeled as some generic column name, but VBA could change it back afterwards.


RE: Sort the headers and rows...hmmm  
Gmen703 : 1/24/2023 10:55 pm : link
In comment 16012976 Gmen703 said:
Quote:
But I would go the VBA route.

Depending on the size of the data (# of cols/rows), I'd throw it into a ListObject (Insert-->table; assuming the headers are unique), create a VBA sort for my ListColumns by name, and then sort the rows. Then, if you need it as a range, convert it back to a range.

I'm guessing the "BLANK" would get re-labeled as some generic column name, but VBA could change it back afterwards.



Something like this in the stackoverflow link...you'd replace "T_PRAS" with whatever name you call your table. That'll sort the headers, and from there, it's just a matter of sorting the rows.

I guess the only question is whether it would sort the headers like 0,1,2,3,4,.... OR
0,1,11,12,13...,2,20,21....etc. If the latter, then you would need to play with converting the header name, which is string, to a value during the comparison.


If CInt(.Item(y).Name) < CInt(.Item(x).Name) Then


Also kill off the ListObject object at the end of the routine

Set tbl = Nothing


VBA-Excel Rearrange columns in alphabetical order - ( New Window )
One thing to note, I believe blanks are put at the end  
BestFeature : 1/24/2023 11:22 pm : link
if you sort by ascending, I had an issue with that and needed an insane work around at my old job.
This may be COMPLETELY WRONG and COMPLETELY NONRESPONSIVE  
glowrider : 1/25/2023 1:07 am : link
But I would really like to know if this works:

Quote:
Here is an Excel formula that will accomplish the task you described:

=SORT(SORT(Sheet1!A:R,1,TRUE),1,TRUE)

This formula first sorts the data in Sheet1!A:R by the first column in ascending order (TRUE), then by the first row in ascending order (TRUE)

You can use this formula to sort the data in a new sheet, or you can use this formula in the same sheet in which you have your raw data.

You can also use VBA macro to sort the data.

Please note that this formula assumes that the data in Sheet1 is contiguous and starts in cell A1. If your data is located in a different range or if it is not contiguous, you will need to adjust the formula accordingly.


This is the result after pasting the question into ChatGPT.

Hope this helps - and if it does, I think we’ll have to start sharpening our pencils for Ye Olde Resume.
Back to the Corner