Compare Values in A and Copy to B
 

The title doesn't quite convey what I'm wanting to do but here's an example.

I have data grouped according to a key field in "A" and I have descriptive
data in "B". That descriptive data can actually be parsed into a possible 5
subsets of data. But some "B" fields don't contain all 5 subsets.

For instance PurTexWhoDis15 might mean, Purchased, from Texas, Wholesaler,
Discount of 15%. But some records in my keyed group might only read PurTex.
They should all read PurTexWhoDis15.

I can run a LEN in column "C" and sort by A ascending and then by C
descending and then write an if statement that says if C4 < C3, B3,""). That
works the first time Len is 6 for C4 and Len is 14 for C3. But is the Len of
C5 is 8 also, it just copies the 6-character expression found in B4.

HERE's What I'm getting (and the spaces to separate the key-sorted data are
in the spreadsheet)

KEY CODE PRODUCT
1111 PURTEXWHODIS15 1111-00
1111 PURTEXWHODIS15 1111-14
1111 PURTEX 1111-05
1111 PURTEX 1111-22
1111 PUR 1111-80


2222 PURTEXWHODIS10 2222-01
2222 PURTEXWHO 2222-04
2222 PURTEXWHO 2222-10
2222 PURTEX 2222-15


Here's what I want to end up with:

KEY CODE PRODUCT
1111 PURTEXWHODIS15 1111-00
1111 PURTEXWHODIS15 1111-14
1111 PURTEXWHODIS15 1111-05
1111 PURTEXWHODIS15 1111-22
1111 PURTEXWHODIS15 1111-80


2222 PURTEXWHODIS10 2222-01
2222 PURTEXWHODIS10 2222-04
2222 PURTEXWHODIS10 2222-10
2222 PURTEXWHODIS10 2222-15

Thanks for any help you can offer.

Compare Values in A and Copy to B
 

Nevermind. I figured out a way to do it. I sorted and grouped by key field
(column A). Then i ran LEN of "B" and posted in Column "C". Then I sorted
by A Ascending / C Descending. Then I ran a macro to separate my keyed
groups by two blank rows. Then, in Column D, I wrote the if statement
=if(A1="",B2,D1). This copied the largest B-field in the keyed array down
through Column D.

The two blank rows caused the if statement to write one too many "B's" and
that extra B was followed by a "0". So I copied the data as values and wrote
another if statement that said if the number one cell to the left and down
one is < 0 then copy the cell to the left. If it is = 0 then enter "0".

Then I did a simple replace "0" down the column, with a blank.

A lot of steps but it worked.

"ConfusedNHouston" wrote:

The title doesn't quite convey what I'm wanting to do but here's an example.

I have data grouped according to a key field in "A" and I have descriptive
data in "B". That descriptive data can actually be parsed into a possible 5
subsets of data. But some "B" fields don't contain all 5 subsets.

For instance PurTexWhoDis15 might mean, Purchased, from Texas, Wholesaler,
Discount of 15%. But some records in my keyed group might only read PurTex.
They should all read PurTexWhoDis15.

I can run a LEN in column "C" and sort by A ascending and then by C
descending and then write an if statement that says if C4 < C3, B3,""). That
works the first time Len is 6 for C4 and Len is 14 for C3. But is the Len of
C5 is 8 also, it just copies the 6-character expression found in B4.

HERE's What I'm getting (and the spaces to separate the key-sorted data are
in the spreadsheet)

KEY CODE PRODUCT
1111 PURTEXWHODIS15 1111-00
1111 PURTEXWHODIS15 1111-14
1111 PURTEX 1111-05
1111 PURTEX 1111-22
1111 PUR 1111-80


2222 PURTEXWHODIS10 2222-01
2222 PURTEXWHO 2222-04
2222 PURTEXWHO 2222-10
2222 PURTEX 2222-15


Here's what I want to end up with:

KEY CODE PRODUCT
1111 PURTEXWHODIS15 1111-00
1111 PURTEXWHODIS15 1111-14
1111 PURTEXWHODIS15 1111-05
1111 PURTEXWHODIS15 1111-22
1111 PURTEXWHODIS15 1111-80


2222 PURTEXWHODIS10 2222-01
2222 PURTEXWHODIS10 2222-04
2222 PURTEXWHODIS10 2222-10
2222 PURTEXWHODIS10 2222-15

Thanks for any help you can offer.