PDA

View Full Version : Solved: Fast Compare using Filter



shmilo
06-22-2008, 01:42 AM
Hi,

I have two sheets.

One has a list of part names in column 1, and the second sheet is for customer used parts in column 1 and quantities in column 2.

I need to find the part number from the customer sheet in the part numbers sheet, and add this quantities to column 2 of the part numbers.

By doing a line by line search, the program time running is large (I have about 5000 parts).

I used the filter in order to see the line with the correct number using:

ValPrice = Cells(ShRow, 2)
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=ValCell



This shows me the row with the number I need.

My question is, if there is a way to get the actual cell or row number of the filtered cell. This way I will be able to change the value of the cell on his right.

If not, is there a fast way to do the search and match? (Fast program execution).

Bob Phillips
06-22-2008, 01:52 AM
Just use formulae

=SUMIF(Customer!A:A,A2,Customer!B:B)

shmilo
06-22-2008, 04:02 AM
Thank you!

I think I didn't explain correct.
See attached a worksheet with the three pages.
Part Numbers - showing a list of all available parts.
Customer A - Example of one of the customers used parts and quantities.
Result - This is how Part numbers will be after running this program.

Of course part numbers is longer, and there are more then one Customer sheets.

Bob Phillips
06-22-2008, 04:25 AM
That formula works in that case.

shmilo
06-22-2008, 04:32 AM
In the excel example as I attached, how the formula should be changed?

First page is Part Numbers, and secon is Customer A.

I tried changing Customer!A:A to Part Numbers!A:A and it didn't work.

Thanks!!

Bob Phillips
06-22-2008, 04:42 AM
No change.

On Results!B3

=SUMIF('Customer A'!A:A,Result!A3,'Customer A'!B:B)

shmilo
06-22-2008, 04:55 AM
Thanks!!