Pulling only certain data

[AndyGray]

Hi Guys, I'm working on a spreadsheet with 500,000 fields so quite a lot of data I need to filter.

Column names are "Customer, Item, Price 1, From1, Price2 From2, Price 3, From3, Price 4, From4" (ABCDEFGHIJ)

This is the data in these fields.

Customer	Item	PRICE1	FROM1	TO1	PRICE2	FROM2	TO2	PRICE3	FROM3	TO3	PRICE4	FROM4	TO4
C1	Product1	1.63	0	9	1.50	10	14	1.25	15	20	0	0	0
C1	Product2	4.68	0	0	0	0	0	0	0	0	0	0	0
C1	Product3	4.68	0	0	0	0	0	0	0	0	0	0	0

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

All this is my bulk data displayed in sheet 1. Price 1 is the price that the customer pays for a product if they purchase between 0 and 9 they pay x amount, if they order 10-14 they pay x amount again etc... the new system doesn't require a range just an amount... So if you order 9 you pay this (and it uses the next "From" price to determine the end of the product)

This data needs inserting into another sheet which has "Customer, Item, Amount (from2,3,4 etc..), Price" So i want a formula which will say "If Price 2 is greater than zero then insert Customer, CardCode, From, and Price" into this field... Then i want it to read price 3 and 4 and add these into the next row down.. To give me something like this

C1, Product1, 10, £1.50
C1, Product1, 15, £1.25
Product 2 and 3 contain 0 in them fields so move onto next value?

Not entirely sure how to work this... I have been using the filter, manually looking for the values which are outside of 0 and pasting it into another sheet... but surley they must be a way to automate this?

Thanks for any help!

Andy

 

[ranman256]

this is really what Microsoft Access database is for. It does this via a simple query.
Doing it in excel is a coding pain in the neck.
you should think about changing your platform.

 

[AndyGray]

I had to do it in excel for data migration from one system to another, all of the templates are made in CSV format. I raised another thread finding another roundabout way to do it and Joe figured a way around it.

Cheers.