If Bx is empty and Ax = Az -> fill Bx with contents of Bz

[jared999]

I've got a strange CSV export of products setup like this:Demo Spreadsheet.xlsx

Column A: Product Handle (Arbitrary)
Column B: Product Name
Column C: Attribute Name

However because of how the document was setup Column B is ONLY FILLED for the first attribute in each product, and the number of attributes is not constant.
What I want to is fill Column B for the entire document, so I need something setup like this:

(x and y are rows)
If Bx is empty AND Ax = Az AND Bz is NOT empty -> fill Bx with By

Any suggestions.

Thanks,

Jared

[newdoverman]

I copied the "Handle" to a new spot on the worksheet and using the Data tab Remove Duplicates removed all duplicates then added the Product Name to the appropriate "Handle".

You say that the Handles are arbitrary. If they are a consistent match with the Product then what I have given you will work.

[jared999]

I copied the "Handle" to a new spot on the worksheet and using the Data tab Remove Duplicates removed all duplicates then added the Product Name to the appropriate "Handle".

You say that the Handles are arbitrary. If they are a consistent match with the Product then what I have given you will work.

I'm not quiet clear on what duplicates you removed since the attributes are all unique?

You are correct that the handle is a consistent match with the product.

What I'm looking for is to have the Product column filled out for every attribute.

-Jared

[newdoverman]

When you copy the "Handle" list the list is:

D8KV
D8KV
D8KV
D8KV
V9IK
V9IK
L7DF
L7DF
L7DF
L7DF
L7DF
L7DF
K5JI
K5JI
K5JI


There are many repeats in that list. With duplicates removed, the list becomes:

D8KV
V9IK
L7DF
K5JI


Then with the product added the vlookup range addressed in the formula will look like this:

D8KV Chevy
V9IK Ford
L7DF Dodge
K5JI Jeep

[jared999]

But then I loose all my attributes. If I wanted a list with the attributes removed I could just reverse sort by Product Name and delete everything below.

I'm looking for a list that has the Product Name on every line with an Attribute Name.

When you copy the "Handle" list the list is:

D8KV
D8KV
D8KV
D8KV
V9IK
V9IK
L7DF
L7DF
L7DF
L7DF
L7DF
L7DF
K5JI
K5JI
K5JI


There are many repeats in that list. With duplicates removed, the list becomes:

D8KV
V9IK
L7DF
K5JI


Then with the product added the vlookup range addressed in the formula will look like this:

D8KV Chevy
V9IK Ford
L7DF Dodge
K5JI Jeep

[XOR LX]

Hi,

Could you just explain a bit more your logical statement: "If Bx is empty AND Ax = Az AND Bz is NOT empty -> fill Bx with By"?

I presume that x,y,z are supposed to indicate consecutive rows, but if I apply this statement to your attachment, I cannot return a single TRUE from this Boolean logic: for example, applied to cell B3 of your attachment, I have:

If B3 is empty (TRUE) AND A3=A5 (TRUE) AND B5 is NOT empty (FALSE) = FALSE

and so on.

Regards

[jared999]

Demo Spreadsheet GOAL.xlsxx, y and z are NOT consecutive. It is a difficult algorythm to write in boolean, I think it probably needs a loop to check the entire spreadsheet for a "sub-header row" which is the first row in a product, the only row that has the product name.

I'm attaching what I'm looking for as the goal (end product).

Hope that helps?

-Jared

Hi,

Could you just explain a bit more your logical statement: "If Bx is empty AND Ax = Az AND Bz is NOT empty -> fill Bx with By"?

I presume that x,y,z are supposed to indicate consecutive rows, but if I apply this statement to your attachment, I cannot return a single TRUE from this Boolean logic: for example, applied to cell B3 of your attachment, I have:

If B3 is empty (TRUE) AND A3=A5 (TRUE) AND B5 is NOT empty (FALSE) = FALSE

and so on.

Regards

[newdoverman]

Did you look at column B in the sheet that I sent to you that had VLOOKUP formulae relating to the small table that looks like this?

D8KV Chevy
V9IK Ford
L7DF Dodge
K5JI Jeep


The data in column B is drawn from this table using a VLOOKUP formula.

[jared999]

Perfect, thank you, VLOOKUP was exactly what I was looking for, much better to use a lookup table than try to write a loop function. I just had to be careful to define the lookup table by the entire column and NOT specify rows. Specifying rows caused the rows to increment as I dragged the formula down column B, which mean some of the table wasn't active for rows further down.

Jared

Did you look at column B in the sheet that I sent to you that had VLOOKUP formulae relating to the small table that looks like this?

D8KV Chevy
V9IK Ford
L7DF Dodge
K5JI Jeep


The data in column B is drawn from this table using a VLOOKUP formula.

[newdoverman]

Thanks for the feedback.

[jared999]

You guys were great with my last question, maybe you can help with this related one:

I've got a spreadsheet, first column is order numbers, second column is order items. If there is more than one item per order, the order number is repeated for each different item on another row. I would like one row per order with the order items separated by a comma in that one box.

Suggestions?Orders Demo Spreadsheet.xlsx

[arlu1201]

Since you marked this thread as solved already, its not likely to be noticed. Its better you create a new thread for your new question.