collect data

[cgriff]

I have data in a sheet i want copy to another sheet it looks like this

Qty
1 data data ect
111 222 333 (this line required)
0 111 2222 444

5 111 3333 222

I want to pick up only the items with a qty that is greater than 0 but I also need the lines of data under this,I would copy this in a new sheet Hope I am making sense.

Thanks in advance

 

[PaddyD]

you can use an advanced filter to identify & isolate records that match your creiteria, but you loose mo on the 'lines under it' bit. Care to post a snapshot of your data (download the html maker addin from the link below this post) & give an example & your expected results.

More details please...

Paddy

 

[cgriff]

On 2002-08-28 14:05, PaddyD wrote:
you can use an advanced filter to identify & isolate records that match your creiteria, but you loose mo on the 'lines under it' bit. Care to post a snapshot of your data (download the html maker addin from the link below this post) & give an example & your expected results.

Chris data.xls
	Q	R	S	T	U
1	Qty	MachiningInfo	X	Y	Z
2	1	border	2450	600	18
3			123	333	333
4			44	11	44
5
6	2	border	643	400	18
7			555	22	77
8	5	border	770	115	18
9	1	border	770	543	18
Chris data

More details please...

Paddy

 

[PaddyD]

Ok - I think I get you...is this right:

Problem

I have 5 columns of data. Column A is 'Quantity'. I need to identify all records where 'Quantity >=1. But, the "quantity column is 'gappy'.


If I've understood you, the easiest way will be to 'fill in the gaps in your data. There is a straightforward trick to do this. See eliW's suggestion in the following:

http://www.mrexcel.com/board/viewtopic.php?topic=19246&forum=2&5

So:

1) use that method to fill the gaps
2) select the newly ofilled-in data, copy it, and paste special values back onto itself to preserve the data
3) Do a filter for quantity > 0

..should get all the records you want.

Paddy

 

[cgriff]

On 2002-08-28 15:00, PaddyD wrote:
Ok - I think I get you...is this right:

Problem

I have 5 columns of data. Column A is 'Quantity'. I need to identify all records where 'Quantity >=1. But, the "quantity column is 'gappy'.


If I've understood you, the easiest way will be to 'fill in the gaps in your data. There is a straightforward trick to do this. See eliW's suggestion in the following:

http://www.mrexcel.com/board/viewtopic.php?topic=19246&forum=2&5

So:

1) use that method to fill the gaps
2) select the newly ofilled-in data, copy it, and paste special values back onto itself to preserve the data
3) Do a filter for quantity > 0

..should get all the records you want.

Paddy

Thanks paddy for your effort this is not what I need as the filled in numbers would cause problems in my data later on,is there a way of collecting the Qty + the group of data ie q2 to q4?

 

[PaddyD]

OK - your data's a mess but I guess we'll have to live with that...

I still think the easiest way to go will be to 'fill in' the data & use a filter. The following gives one method of creating a new column (called 'Num') that does the 'filling in'. Once you've filtered the records, you can delete the column if necessary.

Book4.xls
	A	B	C	D
1	Num	Qty	MachiningInfo	X
2	1	1	border	2450
3	1		123	333
4	1		44	11
5	1
6	2	2	border	643
7	2		555	22
8	5	5	border	770
9	1	1	border	770
10
11
12
Sheet2

"cos of the way you have your data set up (missing values, blank rows in the data) a formula basd approach to get what you want without adding extra columns / filling in the data is going to be complex. If you have an aversion to adding a new column / extra data, a VBA solution might be the best option.

Paddy

 

[cgriff]

Thanks paddy would the code be complicated

 

[PaddyD]

I wouldn't have thought so, but I don't really do vba...here's hoping someone who does is looking...