Excel 2007 : Conditional Formating - Automatically populate column value by grouping

[ngaisteve1]

Say, I have these 4 columns in excel 2007.

Batch No | Code | Count | Remark
-----------------------------------
1010 | ABC | 10 | P1
1010 | ABC | 4 | P2
1030 | CCC | 13 |
1030 | CCC | 2 |
1044 | XYZ | 4 |
1044 | XYZ | 1 |

Right now, I need to manually copy and paste value "P1" and "P2" to each group of same Batch No and Code. I have already sort out the data already. Thus, they are always in pair. Just ask, how can I create a formula using "Conditional Formating" to automatically populate the value of "P1" and "P2" and Remark column?

[Marcol]

Try this in D2

Please Login or Register  to view this content.

or for 2007 and above

Please Login or Register  to view this content.

Drag/Fill Down.

[ngaisteve1]

Thanks. I will give it a try.

[ngaisteve1]

Hi, I tried already. It works but I changed it a bit. Thanks!

[Marcol]

It works but I changed it a bit.

What did you change?

Best you post your solution so that we can all see, and understand, what you were trying to do.

[ngaisteve1]

What did you change?

Best you post your solution so that we can all see, and understand, what you were trying to do.

The formula I have now is

For the first row - =IF(AND((A50446&D50447=A50446&D50447),(E50446<E50447)),"P1","Invalid")
For the second row - =IF(AND(NOT(A50447&D50447=A50448&D50448),(G50446="P1")),"P2","Invalid")

[Marcol]

For the first row - =IF(AND((A50446&D50447=A50446&D50447),(E50446<E50447)),"P1","Invalid")
For the second row - =IF(AND(NOT(A50447&D50447=A50448&D50448),(G50446="P1")),"P2","Invalid")

Wow, I didn't expect that ...

How about posting a sample of your workbook, so that I can understand how your solution is used?

[ngaisteve1]

Wow, I didn't expect that ...

How about posting a sample of your workbook, so that I can understand how your solution is used?

I am afraid the data is a little sensitive. but you may have a glance of it . The "MICR" and "Image" represent "P1" and "P2".untitled.JPG

[Marcol]

Thank you for this glimpse into a secret world, that is an amazingly complex way to solve a simple problem!
You'll sit well with the Admin here, and it will not be long before your wisdom will shine through to all.

All the best
Alistair