NFT: Excel Question - should be easy

thought you said it was going to be easy.

Your knowledge of Excel is obviously far beyond mine.

Good luck.

the source data. Do Data > Sort > first sort Column A by Smallest to Largest and then hit options, change it to left to right, and do the same thing for the row.

In comment 15783078 AcidTest said:

Quote:

thought you said it was going to be easy. Your knowledge of Excel is obviously far beyond mine. Good luck.

It sounds harder than it is. I think a fairly easy solution I should have thought of occurred to me, but it still requires a couple of steps, including cutting and pasting at the end, so I don't love.

You can first sort in ascending order based on the first column, as a normal sort. Then sort again based on the first row, which I don't think I was aware existed as an option. The end result, though, requires me to cut the new last column and paste it as the first column with the row headers.

I was hoping for a formula and/or 1-step solution that doesn't require further interaction with the date (i.e. cut and paste).

In comment 15783099 crackerjack465 said:

Quote:

the source data. Do Data > Sort > first sort Column A by Smallest to Largest and then hit options, change it to left to right, and do the same thing for the row.

Ha. I was just outlining that as you suggested it. I didn't recall sorting horizontally was an option until playing around. There is one additional step, but it works.

A sample of how the data comes to us:

BLANK 16 15 14...0
85 100 99 99 85
84 99 99 98 84
83 99 99 98 84
82 98 98 98 83
.
.
.
0 15 15 14 0

How we need the data:

BLANK 0 1 2 3 ...16
0 0 2 3 5 15
1 2 4 5 7 17
2 3 5 7 9 18
3 5 7 9 10 20
.
.
.
85 85 87 88 90 100

Add a helper column and row. The column should have 1 through 86 or 0 through 85. Then in the row you go from 1 to 17 or 0 through 16.

Once the helper column is there

1. Sort ascending by your helper column
2. Transpose the data
3. Sort by ascending order of your helper row that is now your column
4. Transpose again
5. Remove helper column and row if you want

This is why I like Sheets better. I can put this in a formula once and take care of it.

In comment 15783121 robbieballs2003 said:

Quote:

Add a helper column and row. The column should have 1 through 86 or 0 through 85. Then in the row you go from 1 to 17 or 0 through 16. Once the helper column is there 1. Sort ascending by your helper column 2. Transpose the data 3. Sort by ascending order of your helper row that is now your column 4. Transpose again 5. Remove helper column and row if you want This is why I like Sheets better. I can put this in a formula once and take care of it.

The helper columns are built in; they are header rows and columns. I did this initially, but still involved a cut and paste to transpose, as I have not successfully executed the Transpose function. It just returns a single cell with "0", even when creating the function as an array.

Is this a one time thing or something that is recurring? If it is a one time thing, just get through it. If it is something that is recurring, I can set up a Google Sheet where all you would do is paste in the table and then copy the new table from a different tab and paste it back into excel.

In comment 15783193 robbieballs2003 said:

Quote:

Is this a one time thing or something that is recurring? If it is a one time thing, just get through it. If it is something that is recurring, I can set up a Google Sheet where all you would do is paste in the table and then copy the new table from a different tab and paste it back into excel.

I don't use Google sheets normally. This is recurring, but only a few times a year. It's easy enough to slog through.

In Sheet 1, paste your data.

Create a new tab if there isn't a sheet 2.

In Sheet 2, paste the following in cell A1 ...

=query(transpose(query(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),"Select * Offset 1",0)),"Select * Offset 1",0)

This should give you what you want. Let me know if it works.

In comment 15783247 robbieballs2003 said:

Quote:

In Sheet 1, paste your data. Create a new tab if there isn't a sheet 2. In Sheet 2, paste the following in cell A1 ... =query(transpose(query(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),"Select * Offset 1",0)),"Select * Offset 1",0) This should give you what you want. Let me know if it works.

Yes, with one exception. It removed the header rows/column, which we would want

You said you have help a help column and row. What are they?

Also, are the headers the helper columns? It would be easier if I can see it. I purposely took out the helper row/column.

In comment 15783306 robbieballs2003 said:

Quote:

You said you have help a help column and row. What are they? Also, are the headers the helper columns? It would be easier if I can see it. I purposely took out the helper row/column.

Row 1 is the column headers:
A1=BLANK, B1=16, C1 = 15, D1 = 14,...R1 = 0

COLUMN A are the ROW "Headers":
A1=BLANK
A2 = 85
A3 = 84
A4 = 93
.
.
.
A87 = 0

In comment 15783312 Matt M. said:

Quote:

In comment 15783306 robbieballs2003 said: Quote: You said you have help a help column and row. What are they? Also, are the headers the helper columns? It would be easier if I can see it. I purposely took out the helper row/column. Row 1 is the column headers: A1=BLANK, B1=16, C1 = 15, D1 = 14,...R1 = 0 COLUMN A are the ROW "Headers": A1=BLANK A2 = 85 A3 = 84 A4 = 93 . . . A87 = 0

The actual data spans B2:R87

You still need those numbers? I'll get you the formula.

=transpose(query(arrayformula(value(query(transpose(query({Sheet2!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))

In comment 15783380 robbieballs2003 said:

Quote:

=transpose(query(arrayformula(value(query(transpose(query({Sheet2!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))

Yes, that works, with a typo. the Sheet2!A:EC reference needed to be changed to Sheet1! to reference the original data.

.

In comment 15783409 robbieballs2003 said:

Quote:

.

Thank you!