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) If the limit is 25, and there are less than that I need the rest of the rows to the limit be empty, that is, over write empty if the cells had previous info.
2) If no hits occur, I get an error in the counting loop. I think when it is trying to populate a list, and the list end up with no entries then it generates an error.

Try adding the two blue lines of code where shown near the bottom of the existing code.

  'Put results into 'FF' sheet and sort
  If k = 0 Then k = 1
  With Sheets("SheetFF").Range("G4").Resize(k, cols)
    .Resize(RowsToKeep).ClearContents
    .Value = b

 

[Rixn]

Perfect!
Now, it worx as it should. 1000 thanx!

 

[FREDO]

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?

Hello Sir Peter, Im a fan of yours too. If you remember im not familiar with macro, can u suggest a excel formula to answer the problem using data on those simple table posted in the very first post?Thanks. Fredo

 

[Peter_SSs]

Hello Sir Peter, Im a fan of yours too. If you remember im not familiar with macro, can u suggest a excel formula to answer the problem using data on those simple table posted in the very first post?Thanks. Fredo

I would use a helper column for that task. I've used column H for that (see also next paragraph) and the helper column could be hidden once populated with its formulas.

The formulas in column H require Excel 2007 or later. If you are using an earlier version, or require the solution to work in old and new versions, then use the formulas I have shown in column I.

All formulas in row 2 are copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1								6	6
2	aa	22			22	hh
3	bb	44	1		22	ll		4	4
4	cc	11			33	dd
5	dd	33	1		44	bb		3	3
6	ee	44			44	ii
7	ff	55			77	kk
8	gg	55
9	hh	22	1					1	1
10	ii	44	1					5	5
11
12	kk	77	1					6	6
13	ll	22	1					2	2
14
15
Extract Ordered List

 

[FREDO]

Wow that was so cool, I tried to understand everything you did here but it is a little too complicated for me to digest..Just yet. So far my brain if i got one is not working for me on just this level of complication, how much more on some really complicated examples. I even feel or doubt if this is already the edge of my understanding... or maybe i just need more time and practise in excel applications to pass this level.

Anyway, One more thing Sir Peter if this is not asking too much, as you said in the early post "slowly we are getting there" even though it was not meant for me, yet indeed this is "getting there" for me… im struggling for this quite sometime already.. What are the appropriate formulas if it is possible to make this result on another sheet and the data is in different worksheets, let’s say the data is in sheet1 to sheet3 then the result is on sheet4 as ilustrated below.

Thank you very much your wisdom brightens my day


[TABLE="width: 192, align: left"]
<tbody>[TR]
[TD="colspan: 3"]sheet 1 - Day 1 guests
[/TD]
[/TR]
[TR]
[TD]name
[/TD]
[TD]Age
[/TD]
[TD]Location
[/TD]
[/TR]
[TR]
[TD]allan
[/TD]
[TD]33
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]ben
[/TD]
[TD]50
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]carl
[/TD]
[TD]90
[/TD]
[TD]America
[/TD]
[/TR]
[TR]
[TD]don
[/TD]
[TD]42
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]elma
[/TD]
[TD]75
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]flora
[/TD]
[TD]55
[/TD]
[TD]Africa
[/TD]
[/TR]
[TR]
[TD]gigi
[/TD]
[TD]55
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]helen
[/TD]
[TD]22
[/TD]
[TD]America
[/TD]
[/TR]
[TR]
[TD]ben
[/TD]
[TD]90
[/TD]
[TD]Asia
[/TD]
[/TR]
</tbody>[/TABLE]
















[TABLE="width: 192, align: left"]
<tbody>[TR]
[TD="colspan: 3"]
sheet 2 - Day 2 guests
[/TD]
[/TR]
[TR]
[TD]name
[/TD]
[TD]Age
[/TD]
[TD]Location
[/TD]
[/TR]
[TR]
[TD]jun
[/TD]
[TD]18
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]kyle
[/TD]
[TD]29
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]larry
[/TD]
[TD]44
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]myrna
[/TD]
[TD]67
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]faye
[/TD]
[TD]23
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]oscar
[/TD]
[TD]75
[/TD]
[TD]America
[/TD]
[/TR]
[TR]
[TD]peter
[/TD]
[TD]50
[/TD]
[TD]America
[/TD]
[/TR]
[TR]
[TD]fredo
[/TD]
[TD]45
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]dan
[/TD]
[TD]81
[/TD]
[TD]Asia
[/TD]
[/TR]
</tbody>[/TABLE]
















[TABLE="width: 192, align: left"]
<tbody>[TR]
[TD="colspan: 3"]sheet 3 - Day 4 to 5 guests
[/TD]
[/TR]
[TR]
[TD]name
[/TD]
[TD]Age
[/TD]
[TD]Location
[/TD]
[/TR]
[TR]
[TD]John 1
[/TD]
[TD]18
[/TD]
[TD]Africa
[/TD]
[/TR]
[TR]
[TD]Jane 1
[/TD]
[TD]29
[/TD]
[TD]Africa
[/TD]
[/TR]
[TR]
[TD]John 2
[/TD]
[TD]44
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]Jane 2
[/TD]
[TD]67
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]John 3
[/TD]
[TD]23
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]Jane 3
[/TD]
[TD]75
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]John 4
[/TD]
[TD]50
[/TD]
[TD]Africa
[/TD]
[/TR]
[TR]
[TD]Jane 4
[/TD]
[TD]45
[/TD]
[TD]Europe
[/TD]
[/TR]
[TR]
[TD]John 5
[/TD]
[TD]81
[/TD]
[TD]Asia
[/TD]
[/TR]
</tbody>[/TABLE]















[TABLE="width: 192"]
<tbody>[TR]
[TD="colspan: 3"]Sheet 4 - The Result
(Report on all asian Guest)
[/TD]
[/TR]
[TR]
[TD]name
[/TD]
[TD]Age
[/TD]
[TD]Location
[/TD]
[/TR]
[TR]
[TD]allan
[/TD]
[TD]33
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]don
[/TD]
[TD]42
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]elma
[/TD]
[TD]75
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]ben
[/TD]
[TD]90
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]kyle
[/TD]
[TD]29
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]larry
[/TD]
[TD]44
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]myrna
[/TD]
[TD]67
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]faye
[/TD]
[TD]23
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]fredo
[/TD]
[TD]45
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]dan
[/TD]
[TD]81
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]John 2
[/TD]
[TD]44
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]Jane 2
[/TD]
[TD]67
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD]John 5
[/TD]
[TD]81
[/TD]
[TD]Asia
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks again, God bless.

Fredo

 

[Peter_SSs]

What are the appropriate formulas if it is possible to make this result on another sheet and the data is in different worksheets, let’s say the data is in sheet1 to sheet3 then the result is on sheet4 as ilustrated below.

I would move all the data to Sheet4, either manually or by macro, then use the formulas (or continue to use a macro) to do the task.

 

[FREDO]

Awww.. Just as i guessed, it should be macro... anyway sir if thats what it takes, a macro to transfer these data to another sheet will be fine... can you help me with one?ray:

Thank you.
Fredo

 

[Peter_SSs]

1. Does Sheet4 already exist?

2. If so, does it contain any data?

3. If so, should that data be removed first?

4. Does the data in Sheets 1 to 3 exist in columns A:C with headings in row 1 and data starting in row 2? If not, details please.

5. Your sample results above do not appear to be sorted or have any columns order changes so I'm not really sure what this has to do with the original topic in this thread?

6. Are you simply trying to bring all the, say, "Asia" rows from the other sheets to Sheet4?

7. If not, what is the logic for how you want the results presented?

 

[Rixn]

What does the -- do in SUMPRODUCT(--(C$2:C$20=1),--(B2>B$2:B$20))?

 

[Peter_SSs]

What does the -- do in SUMPRODUCT(--(C$2:C$20=1),--(B2>B$2:B$20))?

(C$2:C$20=1) and (B2>B$2:B20) both produce a set of 19 TRUE/FALSE results. Corresponding elements need to be multiplied together and the results summed (that's what SUMPRODUCT does). However, SUMPRODUCT ignores non-numeric values like TRUE/FALSE. If we can coerce TRUE vales to 1s and FALSE values to 0s then the multiplication/addition can be performed. -- is a good way to do this.

Further reading here and from post #7 onwards here.