Finding corresponding value and replace where its missing

[Makaroni]

I got two rows of random repeating numbers that match, very simplified it looks like this;

A B
123 100
321 200
312 300
321 200
123 100
.......

and it goes on till row 11127 for me.. and the number combination in Col A always corresponds to the same number in Col B, meaning 123 in Col A always gives 100 in Col B.. now there are places in Col B in my matrix that misses its value but got its corresponding value in Col A - something like this.. ;

A B
123 100
321 200
312 300
321 0
123 100
.......

Now, how do I program this in Excel to find the corresponding value B for the value A in the same row and replace the zero with the right nr. (in this example 200), by looking at other value A with value B not being zero?

[JBeaucaire]

You can insert a TITLE row in row1, then turn on the DATA > FILTER > AUTOFILTER, then filter column A by the first number, do a copy/paste down column B to correct the numbers all at once.

Repeat for the next two numbers....done.

Manually... 15 seconds?

[Makaroni]

yea...but the thing is i simplified it A LOT in the example I posted. I have about 11100 rows in the real case, and about 1000 different number combinations... where Col A is a certain Product Number and Col B is the corresponding Price for a product. Now there is in a couple of places in the matrix where the Price is missing for some reason, but the same product exist WITH price somewhere in the same file. I want to find that product, take the price and put it where the price is missing for the same product..

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[JBeaucaire]

If you run just the first 4 lines of code, you'll find the new results in column AB temporarily.

[Makaroni]

I'm having a hard time getting it to work for me.. but do you thing you get my problem I'm struggling to explain?

I'll try to make it simple, even I get a headache understanding it..

So, I have two columns, about 11100 rows long. Column A is a certain product sorted by date sold. Column B shows the price for the products in Col A.

There is about 1000 different products, and the same product tend to show up multiple times in Col A (same type of product sold on a different date).

Now on there are places in Col B that is showing 0 as price, which is incorrect. So I want to find the same product in another row and take the price, just to replace the incorrect 0!

Then find next 0 in Col B, and start over:

1. identify the product nr in Col A (say the prod. nr. is 1122) thats in the same row as the 0 in Col B, meaning that it is missing its price.
2. find another row with the same product nr as in 1. (we said 1122) but WITH the product price in Col B
3. take that price and copy it back into the Col B in 1.

Next zero in ColB

[nilem]

as an option (if the sort of columns A and B are allowed)

Please Login or Register  to view this content.

[JBeaucaire]

Yes, Makaroni, I get the problem. I created a sheet of my own and that's what works for what you've described. It works for me.

Exactly what did you try? Exactly what didn't work?

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. I can run it on your data and see what's up for myself.

[Makaroni]

Thanks a lot!!

I uploaded the file now.. I cut out most of the data to make it easy..

[nilem]

But Prod# 29 619 has two prices in the same group. What value would you like to see in the yellow cells 4.84 or 4.43?

[JBeaucaire]

Here's another take on my approach using formulas via VBA:

Please Login or Register  to view this content.