sort data differently
- Thread starter ahhhmed
- Start date
Cobbled together a solution via various steps.
1. First, we need to generate a list of all unique values. Select the range of items (not prices), then use Ctrl+G, special cells, constants to only select the items and no blanks.
2. Run this macro to create the list (side note, this is a handy macro to have in your personal workbook):
[pre]
[/pre]
3. Outputted list will be vertical and on a new sheet. We can fix that by doing a Copy, Paste Special - Transpose to get it back on correct sheet and in horizontal fashion.
4. Formula time! Assuming original data start in A1, and you pasted your new headers in A10, array formula is:
=IFERROR(INDEX($1:$1,SMALL(IF($A$2:$D$7=A$10,COLUMN($A$2:$D$7)),ROW($A1))),"")
Remember to confirm using Ctrl+Shift+Enter.
5. Copy this formula to the right and down as far as needed.
*IFERROR is a 2007+ function, not available in earlier Excels. Let me know if you don't have this, and we'll come up with a different error trapping method.
1. First, we need to generate a list of all unique values. Select the range of items (not prices), then use Ctrl+G, special cells, constants to only select the items and no blanks.
2. Run this macro to create the list (side note, this is a handy macro to have in your personal workbook):
[pre]
Code:
Sub GetUniqueList()
Dim rCell As Range
Dim colUnique As Collection
Dim sh As Worksheet
Dim i As Long
'only work on ranges
If TypeName(Selection) = "Range" Then
'create a new collection
Set colUnique = New Collection
'loop through all selected cells
'and add to collection
For Each rCell In Selection.Cells
On Error Resume Next
'if value exists, it won't be added
colUnique.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
Next rCell
'make a new sheet to put the unique list
Set sh = ActiveWorkbook.Worksheets.Add
'Write the unique list to the new sheet
For i = 1 To colUnique.Count
sh.Range("A1").Offset(i, 0).Value = colUnique(i)
Next i
'sort with no headers
sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
.Sort sh.Range("A2"), xlAscending, , , , , , xlNo
End If
End Sub3. Outputted list will be vertical and on a new sheet. We can fix that by doing a Copy, Paste Special - Transpose to get it back on correct sheet and in horizontal fashion.
4. Formula time! Assuming original data start in A1, and you pasted your new headers in A10, array formula is:
=IFERROR(INDEX($1:$1,SMALL(IF($A$2:$D$7=A$10,COLUMN($A$2:$D$7)),ROW($A1))),"")
Remember to confirm using Ctrl+Shift+Enter.
5. Copy this formula to the right and down as far as needed.
*IFERROR is a 2007+ function, not available in earlier Excels. Let me know if you don't have this, and we'll come up with a different error trapping method.
Sajan
Excel Ninja
Hello SirJB7,
There are probably many ways to solve this problem with just formulas, and without using any helper cells. Assuming you like puzzles, I will give you a couple of hints to get you started!
Hint #1: Flatten the data into a linear list first
Hint #2: There have been many posts on this forum that describe techniques for flattening a list, including some of my own.
(I can post my solution if we do not see any other responses by Monday.)
Regards,
Sajan.
There are probably many ways to solve this problem with just formulas, and without using any helper cells. Assuming you like puzzles, I will give you a couple of hints to get you started!
Hint #1: Flatten the data into a linear list first
Hint #2: There have been many posts on this forum that describe techniques for flattening a list, including some of my own.
(I can post my solution if we do not see any other responses by Monday.)
Regards,
Sajan.
NARAYANK991
Excel Ninja
Hi ahhhmed ,
Check out the file here for one possibility :
https://www.dropbox.com/s/uz2q20410fnyj35/Ahmed_Example.xlsx
Deriving a unique list of items from the input table is copied from here :
http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/
Narayan
Check out the file here for one possibility :
https://www.dropbox.com/s/uz2q20410fnyj35/Ahmed_Example.xlsx
Deriving a unique list of items from the input table is copied from here :
http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/
Narayan
Sajan
Excel Ninja
@SirJB7,
My apologies for the delayed response. Got back from a trip out of town... and catching up on posts.
Looks like Narayan posted an elegant solution for the problem.
My approach was a bit "quick and dirty"... but here it is:
I have assumed that the header is named as "PriceHeader" and the data is named as "Data", for ease of reference in the formulas.
My source data setup was as follows:
[pre]
[/pre]
The header in the result set is calculated using the following formula (shown for cell H14)
=OFFSET($A$1, INT(MIN(IF( (Data<>"")*ISNA(MATCH(Data, $G$14:G$14,0)), ROW(Data) + COLUMN(Data)%)))-1, ROUND(MOD(MIN(IF( (Data<>"")*ISNA(MATCH(Data, $G$14:G$14,0)), ROW(Data) + COLUMN(Data)%)), 1)*100, 0)-1)
entered with Ctrl + Shift + Enter
The values in the result set is calculated using the following formula (shown for cell H15)
=INDEX(PriceHeader, SMALL(IF(ISNA(MATCH(IF(Data=H$14,COLUMN(Data)), IF(ISNUMBER(MATCH(PriceHeader, H$14:H14, 0)), COLUMN(PriceHeader)), 0)), IF(Data=H$14,COLUMN(Data))),1))
enter with Ctrl + Shift + Enter
Copy the formulas to additional cells. You could wrap it in an IFERROR to suppress the errors.
While writing this, I began to wonder what if I wanted the header to be sorted in alphabetical order.
So here is one approach to get a sorted header.
First the result set (for the same source data), setup starting in cell N3
BELT BOOK CAR EARRING FLASH MEM NECKLACE
3USD 3USD 3USD 4USD 5USD 4USD
4USD 4USD 4USD 5USD #NUM! 5USD
6USD 6USD 5USD #NUM! #NUM! #NUM!
#NUM! #NUM! 6USD #NUM! #NUM! #NUM![/code][/pre]
The sorted header row is calculated using the following formula (shown for cell N3)
=INDIRECT("R" & SUBSTITUTE(LARGE(IF(COUNTIF(Data,">"&Data)=LARGE(IF(COUNTIF($M$3:M3,"<"&Data)>=COUNTA($M$3:M3),COUNTIF(Data,">"&Data)),1),ROW(Data)+COLUMN(Data)%),1), ".","C"),FALSE)
enter with Ctrl + Shift + Enter
The values are calculated using the previous formula (this time shown for cell N4)
=INDEX(PriceHeader, SMALL(IF(ISNA(MATCH(IF(Data=N$3,COLUMN(Data)), IF(ISNUMBER(MATCH(PriceHeader, N$3:N3, 0)), COLUMN(PriceHeader)), 0)), IF(Data=N$3,COLUMN(Data))),1))
enter with Ctrl + Shift + Enter
Cheers,
Sajan.
My apologies for the delayed response. Got back from a trip out of town... and catching up on posts.
Looks like Narayan posted an elegant solution for the problem.
My approach was a bit "quick and dirty"... but here it is:
I have assumed that the header is named as "PriceHeader" and the data is named as "Data", for ease of reference in the formulas.
My source data setup was as follows:
[pre]
Code:
3USD 4USD 5USD 6USD
BOOK EARRING NECKLACE BELT
BELT BOOK FLASH MEM BOOK
BELT
CAR NECKLACE EARRING BOOK
CAR CAR CAR CAR
(My test source data includes some blank cells.)
My result range was setup starting in cell H14
[pre][code]BOOK EARRING NECKLACE BELT FLASH MEM CAR
3USD 4USD 4USD 3USD 5USD 3USD
4USD 5USD 5USD 4USD #NUM! 4USD
6USD #NUM! #NUM! 6USD #NUM! 5USD
#NUM! #NUM! #NUM! #NUM! #NUM! 6USDThe header in the result set is calculated using the following formula (shown for cell H14)
=OFFSET($A$1, INT(MIN(IF( (Data<>"")*ISNA(MATCH(Data, $G$14:G$14,0)), ROW(Data) + COLUMN(Data)%)))-1, ROUND(MOD(MIN(IF( (Data<>"")*ISNA(MATCH(Data, $G$14:G$14,0)), ROW(Data) + COLUMN(Data)%)), 1)*100, 0)-1)
entered with Ctrl + Shift + Enter
The values in the result set is calculated using the following formula (shown for cell H15)
=INDEX(PriceHeader, SMALL(IF(ISNA(MATCH(IF(Data=H$14,COLUMN(Data)), IF(ISNUMBER(MATCH(PriceHeader, H$14:H14, 0)), COLUMN(PriceHeader)), 0)), IF(Data=H$14,COLUMN(Data))),1))
enter with Ctrl + Shift + Enter
Copy the formulas to additional cells. You could wrap it in an IFERROR to suppress the errors.
While writing this, I began to wonder what if I wanted the header to be sorted in alphabetical order.
So here is one approach to get a sorted header.
First the result set (for the same source data), setup starting in cell N3
BELT BOOK CAR EARRING FLASH MEM NECKLACE
3USD 3USD 3USD 4USD 5USD 4USD
4USD 4USD 4USD 5USD #NUM! 5USD
6USD 6USD 5USD #NUM! #NUM! #NUM!
#NUM! #NUM! 6USD #NUM! #NUM! #NUM![/code][/pre]
The sorted header row is calculated using the following formula (shown for cell N3)
=INDIRECT("R" & SUBSTITUTE(LARGE(IF(COUNTIF(Data,">"&Data)=LARGE(IF(COUNTIF($M$3:M3,"<"&Data)>=COUNTA($M$3:M3),COUNTIF(Data,">"&Data)),1),ROW(Data)+COLUMN(Data)%),1), ".","C"),FALSE)
enter with Ctrl + Shift + Enter
The values are calculated using the previous formula (this time shown for cell N4)
=INDEX(PriceHeader, SMALL(IF(ISNA(MATCH(IF(Data=N$3,COLUMN(Data)), IF(ISNUMBER(MATCH(PriceHeader, N$3:N3, 0)), COLUMN(PriceHeader)), 0)), IF(Data=N$3,COLUMN(Data))),1))
enter with Ctrl + Shift + Enter
Cheers,
Sajan.