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 am NOT transposing the Columns with the Rows.
That does work.
Is there a similar solution for Excel directly?
I know. Not much help. Is the Google solution not adequate?
I know. Not much help. Is the Google solution not adequate?
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.
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.
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.
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.
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 )
=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.