Hi, I have two columns: product_id and list_price. Each product has a unique list_price and its own product_id, but appears multiple times. However, many of the rows have a product_id of 0, they are sorted from 0 to 5 lowest to highest. I need to replace the product_id cells that have a 0 in them with the correct product_id , which can be found in the same column by using the list_price column value. How do I do this for thousands of rows without having to do it manually?
Do you mean that when there is a 0 product ID, if you look for that same price later in the list, it will match the actual product ID? One challenge is that you have many zeroes with the same price so you have to find the first non-zero product ID with that price.
Is it guaranteed that no two products can ever have the same price?
You can do this by adding another column with the result, then copying values back to column 1.
C2 and fill down:
Formula:
Please Login or Register to view this content.
This might seem ballsy of me, Jeff, but could you explain it to me? I tried it, it works great, but I'd love an explanation. I am studying to be an analyst and struggle with formulas.
Yeah I tried it on the larger dataset and it doesn't work. I wonder why
Very reasonable question.
I am going to work from the outside in and give the segments names
=INDEX(OFFSET(A$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1")),MATCH(B2,OFFSET(B$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1"))))
COUNTIF(A:A,">=1") nonzero_cell_count
This counts how many cells in column A have a value of 1 or more. This is so we can count the non-zero cells.
MATCH(1,A:A,0) row_with_first_1
The row number of the row in column A that has the first 1
OFFSET is a function that takes the initial range, moves it by the specified number of rows and columns, then sets the height of the result (number of rows). It can also set the width in columns but I am not using that here.
OFFSET(A$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1"))
Using our names we get
OFFSET(A$1,row_with_first_1-1,0,nonzero_cell_count) all_nonzero_product_IDs
So this starts with A1, and then moves down to the row with the first 1, and includes all the rows with nonzero numbers.
OFFSET(B$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1")) prices_for_nonzero_product_IDs
This is just like the previous one, but gives us the corresponding prices in column B.
MATCH(B2,OFFSET(B$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1")))
Replacing the name we get
MATCH(B2,prices_for_nonzero_product_IDs) desired_price_row
This finds the row number with the price ID we're looking for
Now back to our main formula
INDEX(OFFSET(A$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1")),MATCH(B2,OFFSET(B$1,MATCH(1,A:A,0)-1,0,COUNTIF(A:A,">=1"))))
Replacing our names we get
INDEX(all_nonzero_product_IDs,desired_price_row))
This takes all nonzero product IDs, and returns the one in the row with the desired price.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks