Extract some rows to a new sorted list?

[Rixn]

I have a list with three columns:

aa 22
bb 44 1
cc 11
dd 33 1

and I want to create this list:

33 dd
44 bb

..that is, for only the rows with value 1 in the third column

Additional needs:
- Sorted by the second column (lowest on top)
- Switched column order

 

[Peter_SSs]

1. What type of approach are you looking for? Macro, formula or manual?

2. Is it possible that 2 or more rows could have the same number in the second column and a 1 in the third column?

3. Does the data actually have headings above what you have shown?

 

[Rixn]

1) Formula, but can be in a macro too, since I do have a button click in which I already execute some macros. If the extracting and sorting can't be done as formula, maybe the sorting have to be in the macro?
2a) Yes, the values can be the same value
2b) Yes, some of the rows have 1 and some don't. I only want to pick out those who have 1, that is why I have that column.
3 Yes I have headings above the column.

As you might guess my example is a simplified version of what I really need. I will apply the answer to my far more complex situation. My experience is that a simple example gives faster answer, and the answer will also be more clear for me to understand so I can apply it. This mean for example that I really have a lot more columns, and the content in them os not aa, bb or 11, 22 or 1. The third column actualy have four different values, 00, 01, 10 and 11 where it is 10 I want to extract. But for the knowledge transfer here it is easier to just use 1.

 

[Peter_SSs]

My experience is that a simple example gives faster answer, and the answer will also be more clear for me to understand so I can apply it.

My experience is that this can also lead to inappropriate suggestions and back-tracking to get to the desired result. Lets hope you are right in this instance.

Since you already have code doing something, I would approach this as a code task. Here's my suggestion for testing in a copy of your workbook.

Sub NewList()
  Dim Cols As Long
  Dim rCrit As Range
  
  Application.ScreenUpdating = False
  Cols = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
  Set rCrit = Cells(1, Cols + 1).Resize(2)
  rCrit.Cells(1).Value = Cells(1, Cols).Value
  rCrit.Cells(2).Value = 1
  Columns(1).Resize(, Cols).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, _
      CopyToRange:=rCrit.Cells(1).Offset(, 1), Unique:=False
  rCrit.ClearContents
  With rCrit.Resize(1).Offset(, 1).CurrentRegion
    .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
      MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Columns(2).Cut
    .Columns(1).Insert
    .Columns(.Columns.Count).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

For original data in columns A:C, the above code produced the results in columns E:F.

Excel Workbook
	A	B	C	D	E	F	G
1	H1	H2	H3		H2	H1
2	aa	22			33	dd
3	bb	44	1		33	ff
4	cc	11			38	hh
5	dd	33	1		44	bb
6	ee	44	1		44	ee
7	ff	33	1
8	gg	38
9	hh	38	1
10
Extract List

 

[Rixn]

Hehe, your experience is better than mine in this case. I do have trouble extracting the key parts and applying on my real situation. My VBA skills aren't strong enough for this. Maybe for an easier, but not so smart, code solution?

I normally learn a lot when an answer is easy to adapt into my oown code, as oppose to just use it to get it to work. Probably that is the way I have to do it now.

Let me see if I can provide my situation in a way you can understand.

A shortcut would be to provide the key parts in your given code, but I can't really ask that of you. I should increase my skill level through the normal chanels step by step instead.

..and thanx for the link to Excel Jeanie. I'll try it next time.

 

[Peter_SSs]

Since you said you had a lot of columns, I didn't know ..

- How many columns you had. (I used code to find how many)
- Which column of the many was the one you wanted to filter on. (I guessed the last one like your sample)
- Whether the result was to include all the columns except the one used to filter or just two of the columns. If just two, which two? (I guessed you wanted all except the filter one)
- Which of the many was the one to used to sort the results. (I guessed the second one of the original columns like your sample)
- Which two columns of the many had to have their order switched. (I guessed the first two like your sample)

So you see there had to be quite a few guesses so it was unlikely I guessed all of them correctly, and quite possibly all of my guesses were wrong.

You could elaborate on the points above or try my approach (outlined below) manually, with the macro recorder on, to see if you could get something that you could then modify to suit.

My basic approach was to ..

1. Use Advanced Filter to get the rows with "1" in the relevant column to a new list at the right of the other data.
2. Sort the new data based on the relevant column you want.
3. Swap the columns to the correct order.
4. Clear the column in the new list that contained the "1"s

If you have more columns in the original data that are not wanted in the final list, they could probably be deleted as part of step 4.

 

[Rixn]

I have the following in sheet "SheetSL", the range is row $A10:$A7000:

00 HTML Conversions.xlsm
	A	B	C	D	E	F	G	H
9	Ga	Ep	Wo	Le	Di	Pl	Po	Index
10	January	Blue	1	1	77,0%	11,0%	88,0	0
11	January	Blue	1	2	93,1%	4,2%	39,2	11
12	January	Blue	2	1	95,2%	8,3%	79,3	10
13	January	Blue	2	2	40,1%	6,3%	25,3	11
14	January	Red	1	1	80,8%	6,0%	48,4	11
15	January	Red	1	2	14,2%	2,3%	3,2	11
16	January	Yellow	1	1	94,5%	4,4%	41,4	10
17	January	Yellow	1	2	63,9%	6,8%	43,6	10
18	January	Yellow	1	3	33,6%	2,7%	9,0	11
19	February	Green	1	1	75,0%	11,3%	85,0	11
20	February	Green	1	2	39,1%	2,5%	9,9	0
21	February	Green	2	1	40,5%	5,9%	23,9	0
22	February	Green	2	2	96,2%	20,7%	11,0	10
23	February	Gray	1	1	64,1%	17,4%	111,5	0
24	February	Gray	2	1	77,0%	13,2%	33,0	10
25	February	Gray	2	2	94,8%	20,2%	191,4	11
Sheet4

The BM column (Index) is the one which is supposed to filter out which rows I want. Only the one with the value 10 is supposed to be picked.

I want this in sheet "SheetFF":

00 HTML Conversions.xlsm
	F	G	H	I	J	K	L	M
4	*	Po	Pl	Di	Ga	Ep	Wo	Le
5	1	11,0	20,7%	96,2%	February	Green	2	2
6	2	33,0	13,2%	77,0%	February	Gray	2	1
7	3	41,4	4,4%	94,5%	January	Yellow	1	1
8	4	43,6	6,8%	63,9%	January	Yellow	1	2
9	5	79,3	8,3%	95,2%	January	Blue	2	1
Sheet5

The rows should be sorted (increasing) by the G column (Po). ..and, I don't need the INdex column to follow.

I think the visual tables here answers many of the questions you had. If there're still some uncertencies please let me know?<br />

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4 </a>

 

[Peter_SSs]

I think the visual tables here answers many of the questions you had. If there're still some uncertencies please let me know?

Yes, that's good thanks. I'm not quite sure how I'll attack it so a couple more questions to help decide.

1. SheetSL:
- Are the hidden columns in your screen shot actually hidden in your sheet, or just hidden for this screen shot?
- Do the hidden columns contain headings & data? I don't need to know what the headings are, just if they exist.
- Do any of the visible columns contain formulas relating to the hidden columns. That is, if I was to delete the hidden columns (in a copy of the worksheet) would that ruin any of the visible data?

2. SheetFF:
- Can I assume this sheet exists and is empty (at least from row 3 down?
- Is the code supposed to be producing the values in column F?

 

[Rixn]

1a) Since they do not have any impact on this issue I just didn't show them here. It would be a wide example to show the columns all the way to the BM column. I'm not sure what you mean with "hidden" columns, but in the real sheet I have done nothing to hide any column.

1b) Some columns have headings, some don't. ..and some columns have lists the same way the columns shown here, but others don't follow that structure, and some don't have any information at all. I know I sometimes get into trouble when I delete a column. I've tried to be dynamic in my formulas, but I haven't succeded 100%, and for my macros I have all column references hard coded (that's the skill level I'm in - to keep it simple, but workable, not neccesarily the best solution). Now, my layout in all the sheets are coming to a setteled state, so there are not so many changes when it comes to columns.

1c) Yes, very much so - all cells have a formula. The copy&paste have to be only values.

2a) yes it exists, and is empty from row 4 and down. The headings is already in row 3. I have info in cells B2 to D30 (will be up to D50 when I'äm finished), and som info "stored away" far away in W01 to W14.

2b) F column is fixed (since very few posts in the sorting column will end up with the same value so there is no need to make the placing numbers take that into consideration (too much work too gain very little).

 

[Peter_SSs]

This should be getting pretty close. It does assume nothing is to the right of column BM in 'SheetSL'. If that is not the case, a further small tweak may be required.

I've added comments to the code so you can get an idea of the steps.
If you are not already aware, you can go to the code and step through it a line at a time by repeatedly pressing F8. This way you can go back to the sheet(s) and see the results of the previous line of code before returning to the vba window and pressing F8 again. This often helps with understanding, debugging, tweaking.

Sub NewList()
  Dim i As Long
  Dim rCrit As Range
  
  Const DelRng As String = "E:W,Y:Z,AC:BL" '<- Cols in SheetSL not required
  Const FilterVal As Long = 10 '<- Value you want to filter on
  
  Application.ScreenUpdating = False
  'Make a copy of 'SL' sheet so we can do what we like with the copy
  Sheets("SheetSL").Copy Before:=Sheets(1)
  'Use this COPY sheet
  With ActiveSheet
    'Replace Formulas with their values
    With .UsedRange
      .Value = .Value
    End With
    'Delete the unwanted columns
    .Range(DelRng).Delete
    'Data of interest should now be in A9:Hxx so use that
    With .Range("A9", .Range("A" & Rows.Count).End(xlUp)).Resize(, 8)
      'Sort on col 'Po'
      .Sort Key1:=.Cells(1, 7), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
      MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      'Move Po, Pl and Di to the left
      For i = 7 To 5 Step -1
        .Columns(i).Cut
        .Cells(1, 1).Insert Shift:=xlToRight
      Next i
    End With
    'Data of interest should be in A9:Hxx but cells have moved
    'due to col moves so reset this reference
    With .Range("A9", .Range("A" & Rows.Count).End(xlUp)).Resize(, 8)
      'Set Adv Filter criteria cells to just right of the data area
      Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
      'Fill the criteria cells with heading and filter value
      rCrit.Value = Application.Transpose(Array("Index", FilterVal))
      'Do the Adv Filter and put results to the right of existing data
      .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, _
      CopyToRange:=rCrit.Offset(, 2).Resize(1)
    End With
    'Copy filter results (excluding headings and Index colum) to 'FF' sheet
    rCrit.Offset(, 2).CurrentRegion.Offset(1).Resize(, 7).Copy _
    Destination:=Sheets("SheetFF").Range("G4")
    'Delete the tempory (copy) worksheet
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
  End With
  'Activate 'FF' to view results
  Sheets("SheetFF").Activate
  Application.ScreenUpdating = True
End Sub