Conversation Re: How would I sort/filter this example? Excel 2016 in Excel
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828507#M39077
<P><LI-USER uid="377119"></LI-USER> </P><P> </P><P>I have used VBA approach to get the data in the desired format.</P><P>In the attached, click the button called "Transform Data" on Sheet1 to run the code. You will be prompted to select the range with data and the code will insert a new sheet called "Output" with the data in the desired format.</P><P> </P><LI-CODE lang="markup">Sub TransformData()
Dim wsOutput As Worksheet
Dim rng As Range
Dim x As Variant
Dim y() As Variant
Dim i As Long
Dim j As Long
Dim r As Long
Dim c As Long
Dim n As Variant
On Error Resume Next
Set rng = Application.InputBox("Please select the Range with Data.", Type:=8)
If rng Is Nothing Then
MsgBox "You didn't select any Range.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Output").Delete
On Error GoTo 0
x = rng.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 1)
r = 1
c = 1
For i = 1 To UBound(x, 1)
For j = 2 To UBound(x, 2)
If IsNumeric(x(i, j)) And x(i, j) > 0 Then
n = Application.Match(x(i - 1, j), Application.Index(y, 1, 0), 0)
If IsError(n) Then
r = r + 1
c = c + 1
ReDim Preserve y(1 To UBound(x, 1) * UBound(x, 2), 1 To c)
y(r, 1) = x(i - 1, 1)
y(1, c) = x(i - 1, j)
y(r, c) = x(i, j)
Else
r = r + 1
y(r, 1) = x(i - 1, 1)
y(r, n) = x(i, j)
End If
End If
Next j
Next i
Set wsOutput = ThisWorkbook.Worksheets.Add(after:=rng.Parent)
wsOutput.Name = "Output"
With wsOutput.Range("A1").Resize(r, c)
.Value = y
.NumberFormat = "0.00"
.Columns(1).AutoFit
.Borders.Color = vbBlack
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub</LI-CODE><P> </P><P> </P>Thu, 29 Aug 2019 05:25:04 GMTSubodh_Tiwari_sktneer2019-08-29T05:25:04ZHow would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/827743#M39054
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Excel Example.png" style="width: 924px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/128970i73D194EAB5F2B788/image-size/large?v=1.0&px=999" title="Excel Example.png" alt="Excel Example.png" /></span></P><P>Hello guys, I'm in need of some excel expertise. I've attached a sample picture of what my current spreadsheet looks like.</P><P> </P><P><STRONG>What I'm trying to do: </STRONG>I have multiple abbreviations with an amount under columns B through G. </P><P>I'm trying to sort all the JACM/CLAM/WYAK/etc and the numbers below them into their own column, and I'm having alot of trouble doing so. I want Column B to be all JACM, C to be all WYAK, etc but still be lined up on correct guys under row A.</P><P> </P><P>Thanks, sorry for the confusing explanation</P>Wed, 28 Aug 2019 18:15:45 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/827743#M39054Paneross2019-08-28T18:15:45ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828345#M39075
<P><LI-USER uid="377119"></LI-USER> The problem here is that you have entered data into a report structure, and on top of that used inconsistent column patterns. This data is now really hard to handle, and re-arranging it will be tedious and most likely, manual.</P>
<P> </P>
<P>A far better data entry structure would be just three columns, i.e. name, type, and value. From such a flat table you can then easily build the desired report with the data arranged as you describe.</P>Thu, 29 Aug 2019 00:49:19 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828345#M39075Ingeborg Hawighorst2019-08-29T00:49:19ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828507#M39077
<P><LI-USER uid="377119"></LI-USER> </P><P> </P><P>I have used VBA approach to get the data in the desired format.</P><P>In the attached, click the button called "Transform Data" on Sheet1 to run the code. You will be prompted to select the range with data and the code will insert a new sheet called "Output" with the data in the desired format.</P><P> </P><LI-CODE lang="markup">Sub TransformData()
Dim wsOutput As Worksheet
Dim rng As Range
Dim x As Variant
Dim y() As Variant
Dim i As Long
Dim j As Long
Dim r As Long
Dim c As Long
Dim n As Variant
On Error Resume Next
Set rng = Application.InputBox("Please select the Range with Data.", Type:=8)
If rng Is Nothing Then
MsgBox "You didn't select any Range.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Output").Delete
On Error GoTo 0
x = rng.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 1)
r = 1
c = 1
For i = 1 To UBound(x, 1)
For j = 2 To UBound(x, 2)
If IsNumeric(x(i, j)) And x(i, j) > 0 Then
n = Application.Match(x(i - 1, j), Application.Index(y, 1, 0), 0)
If IsError(n) Then
r = r + 1
c = c + 1
ReDim Preserve y(1 To UBound(x, 1) * UBound(x, 2), 1 To c)
y(r, 1) = x(i - 1, 1)
y(1, c) = x(i - 1, j)
y(r, c) = x(i, j)
Else
r = r + 1
y(r, 1) = x(i - 1, 1)
y(r, n) = x(i, j)
End If
End If
Next j
Next i
Set wsOutput = ThisWorkbook.Worksheets.Add(after:=rng.Parent)
wsOutput.Name = "Output"
With wsOutput.Range("A1").Resize(r, c)
.Value = y
.NumberFormat = "0.00"
.Columns(1).AutoFit
.Borders.Color = vbBlack
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub</LI-CODE><P> </P><P> </P>Thu, 29 Aug 2019 05:25:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828507#M39077Subodh_Tiwari_sktneer2019-08-29T05:25:04ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828944#M39089
<P><LI-USER uid="394231"></LI-USER> that was exactly what I was looking for! Thank you so much, you don't realize how much this will help now and in the future. I truly appreciate it!</P>Thu, 29 Aug 2019 12:11:33 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828944#M39089Paneross2019-08-29T12:11:33ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828953#M39091
<P><LI-USER uid="377119"></LI-USER> </P><P>This is an optimum situation for using Power Query, just few clicks.</P><P>Good luck</P><P>Nabil Mourad</P>Thu, 29 Aug 2019 12:16:00 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828953#M39091nabilmourad2019-08-29T12:16:00ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828986#M39093
<P><LI-USER uid="377119"></LI-USER> </P><P> </P><P>You're welcome! Glad it worked as desired!</P>Thu, 29 Aug 2019 12:27:25 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828986#M39093Subodh_Tiwari_sktneer2019-08-29T12:27:25ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828995#M39094
<P><LI-USER uid="365248"></LI-USER> </P><P> </P><BLOCKQUOTE><HR /><LI-USER uid="365248"></LI-USER> wrote:<P>This is an optimum situation for using Power Query, just few clicks.</P></BLOCKQUOTE><P>This is absolutely pointless to criticize the accepted solution instead of posting your own solution., that makes no sense.<BR /><BR /></P>Thu, 29 Aug 2019 12:33:44 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/828995#M39094Subodh_Tiwari_sktneer2019-08-29T12:33:44ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829057#M39096
<P><LI-USER uid="394231"></LI-USER> You've been a great help, would you mind helping with this last question then I think I'll be set for a very long time. Now that I got the data sorted using the code provided, I've noticed I have duplicates, (see example "Problem Table"), now I'm trying to merge multiple entries in Column A, with the B:M data into one row. I've tried merging, subtotaling, and played with a pivot table and wasn't able to merge the rows, and get rid of the duplicates. Thanks!<span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Problem Table.png" style="width: 999px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129088i428162A944AD1A21/image-size/large?v=1.0&px=999" title="Problem Table.png" alt="Problem Table.png" /></span><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="End Goal.png" style="width: 999px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129087iAE1BC5E43CECACD1/image-size/large?v=1.0&px=999" title="End Goal.png" alt="End Goal.png" /></span></P>Thu, 29 Aug 2019 13:18:12 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829057#M39096Paneross2019-08-29T13:18:12ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829259#M39099
<P><LI-USER uid="394231"></LI-USER> </P><P> </P><P>Can you show me where I criticized your solution?</P><P>I am reporting this comment as a rude comment.</P><P> </P>Thu, 29 Aug 2019 14:51:16 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829259#M39099nabilmourad2019-08-29T14:51:16ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829490#M39102
<P><LI-USER uid="394231"></LI-USER> Hello, our moderation team has reviewed this and seems like a misunderstanding. Offering different advice to what's been accepted is not the same as criticizing that previous advice. Check out our <A href="https://techcommunity.microsoft.com/t5/Community-Guidelines/Microsoft-Tech-Community-Code-of-Conduct/m-p/31390#M14" target="_self">code of conduct</A> if you have any questions. </P>Thu, 29 Aug 2019 15:39:35 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829490#M39102Eric Starker2019-08-29T15:39:35ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829528#M39103
<P><LI-USER uid="41707"></LI-USER> </P><P>Sorry Eric if I violated the code of conduct. Please accept my apologies.</P>Thu, 29 Aug 2019 16:00:16 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/829528#M39103Subodh_Tiwari_sktneer2019-08-29T16:00:16ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830010#M39111
<P><LI-USER uid="377119"></LI-USER> </P><P> </P><P>In that case, you may try this...</P><P> </P><LI-CODE lang="markup">Sub TransformData()
Dim wsOutput As Worksheet
Dim rng As Range
Dim x As Variant
Dim y() As Variant
Dim i As Long
Dim j As Long
Dim r As Long
Dim c As Long
Dim n As Variant
Dim m As Variant
On Error Resume Next
Set rng = Application.InputBox("Please select the Range with Data.", Type:=8)
If rng Is Nothing Then
MsgBox "You didn't select any Range.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Output").Delete
On Error GoTo 0
x = rng.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 1)
r = 1
c = 1
For i = 1 To UBound(x, 1)
For j = 2 To UBound(x, 2)
If IsNumeric(x(i, j)) And x(i, j) > 0 Then
If i > 1 Then m = Application.Match(x(i - 1, 1), Application.Index(y, 0, 1), 0)
n = Application.Match(x(i - 1, j), Application.Index(y, 1, 0), 0)
If IsError(n) Then
c = c + 1
ReDim Preserve y(1 To UBound(x, 1) * UBound(x, 2), 1 To c)
If IsError(m) Then
r = r + 1
y(r, 1) = x(i - 1, 1)
y(r, c) = x(i, j)
Else
y(m, c) = x(i, j)
End If
y(1, c) = x(i - 1, j)
Else
If IsError(m) Then
r = r + 1
y(r, n) = x(i, j)
Else
y(m, n) = x(i, j)
End If
y(r, 1) = x(i - 1, 1)
End If
End If
Next j
Next i
Set wsOutput = ThisWorkbook.Worksheets.Add(after:=rng.Parent)
wsOutput.Name = "Output"
With wsOutput.Range("A1").Resize(r, c)
.Value = y
.NumberFormat = "0.00"
.Columns(1).AutoFit
.Borders.Color = vbBlack
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub</LI-CODE><P> </P><P> </P>Thu, 29 Aug 2019 19:32:02 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830010#M39111Subodh_Tiwari_sktneer2019-08-29T19:32:02ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830037#M39112
<P><LI-USER uid="394231"></LI-USER> </P><P> </P><P>You sir have saved the day, thank you very much!</P>Thu, 29 Aug 2019 19:54:10 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830037#M39112Paneross2019-08-29T19:54:10ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830569#M39121
<P><LI-USER uid="365248"></LI-USER> Maybe you can post the Power Query approach? That would help people who don't want to delve in to VBA.</P>Fri, 30 Aug 2019 02:10:17 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830569#M39121Ingeborg Hawighorst2019-08-30T02:10:17ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830600#M39125
<P><LI-USER uid="377119"></LI-USER> </P><P>You're welcome again!</P>Fri, 30 Aug 2019 03:31:21 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/830600#M39125Subodh_Tiwari_sktneer2019-08-30T03:31:21ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/831127#M39146
<P><LI-USER uid="7724"></LI-USER> </P>
<P>If with Power Query. To simplify I took the source as the table, not as named range.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 399px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129342i4BF15ED1F8219AAF/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>In Power Query for the source table index every odd/even row</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 786px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129343i60430227A1167F4C/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>When reference it and, if only main steps, filter only rows with codes, unpivot other than names columns, add index, after that sort by codes and names</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 326px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129344iE1431D2BC516ECBD/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>Another reference with exactly same steps, but here the only select numbers. Merge first reference with second one on index, extract numbers only from column with tables from second reference. It looks like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 338px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/129345iFF555A5F2E65D5D1/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>Pivot it on codes for Value.1 without aggregation and load result into Excel.</P>
<P> </P>
<P>With some coding that could be done more compact and combined in one query, but I tried to use UI only.</P>
<P>More details are in attached file.</P>Fri, 30 Aug 2019 12:39:23 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/831127#M39146Sergei Baklan2019-08-30T12:39:23ZRe: How would I sort/filter this example? Excel 2016
https://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/831184#M39149
<P>Good one <LI-USER uid="521"></LI-USER></P>Fri, 30 Aug 2019 13:15:42 GMThttps://techcommunity.microsoft.com/t5/excel/how-would-i-sort-filter-this-example-excel-2016/m-p/831184#M39149Subodh_Tiwari_sktneer2019-08-30T13:15:42Z