ExcelBanter - Values on the fly

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Values on the fly (https://www.excelbanter.com/excel-discussion-misc-queries/216538-values-fly.html)

Values on the fly

I'm looking for a way to automatically assign an 'index value' to cells in
column "index". Starting with row one, I want to assign "A" based on the
contents
of the "item#" and "country" cells in the same row. And then, I want "A"
assigned to
every row containing the same values in those columns. And then, I want the
next different value (in this case, the 2nd row) to be assigned "B"...again
with every other row containing the same values in the "Item#" and "county"
columns to be assigned the index value of "B"....and so on and so on. Is
there a function that can be combined with a logic statement to automatically
assign values to all cells in the "index" column?

Index Order Item#
country
1 8502 1 4215.00 4,215.00 TW
2 8943 1 1802.00 1,802.00 TW
3 2251 1 188.00 188.00 CN
4 2251 1 935.00 935.00 CN
5 2251 1 450.00 450.00 CN
6 2251 1 25.00 25.00 CN
7 2251 2 701.38 1,402.76 CN
8 2251 1 179.57 179.57 CN
9 2251 2 50.00 100.00 CN
10 2251 14 100.00 1,400.00 CN
11 2251 1 1800.00 1,800.00 TW
12 2251 1 100.00 100.00 TW
13 2251 1 25.00 25.00 TW
14 2251 1 56.45 56.45 TW
15 2251 1 435.00 435.00 TW
16 2251 1 235.20 235.20 TW
17 2251 1 538.00 538.00 TW
18 2251 3 174.45 523.35 TW
19 2251 1 1923.00 1,923.00 TW
20 2251 1 843.00 843.00 TW
21 2251 1 400.00 400.00 CN
22 2251 1 400.00 400.00 CN
23 2251 1 3434.00 3,434.00 CN
24 2251 1 942.89 942.89 CN
25 8925 1 225.00 225.00 CN
26 7234 2 1307.00 2,614.00 JP
27 8925 1 1395.00 1,395.00 TW
28 2251 1 632.70 632.70 TW
29 6102 1 8500.00 8,500.00 TW
30 7616 1 1822.00 1,822.00 TW

Values on the fly

One idea ..

Assuming data as posted starts in row2 down,
col C = item#
col G = country abbrev/code

Put in A2:
=IF(COUNTA(C2,G2)<2,"",C2&"-"&G2&"
"&"Order#:"&TEXT(SUMPRODUCT((C$2:C2=C2)*(G$2:G2=G2 )),"0000"))
Copy down as far as required to generate a unique text string index which
serializes the orders by the desired item#-country code,
viz in A2 down will appear:

8502-TW Order#:0001
8943-TW Order#:0001
2251-CN Order#:0001
2251-CN Order#:0002
2251-CN Order#:0003
etc

Adapt the expression to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Cosmo" wrote:
I'm looking for a way to automatically assign an 'index value' to cells in
column "index". Starting with row one, I want to assign "A" based on the
contents
of the "item#" and "country" cells in the same row. And then, I want "A"
assigned to
every row containing the same values in those columns. And then, I want the
next different value (in this case, the 2nd row) to be assigned "B"...again
with every other row containing the same values in the "Item#" and "county"
columns to be assigned the index value of "B"....and so on and so on. Is
there a function that can be combined with a logic statement to automatically
assign values to all cells in the "index" column?

Index Order Item#
country
1 8502 1 4215.00 4,215.00 TW
2 8943 1 1802.00 1,802.00 TW
3 2251 1 188.00 188.00 CN
4 2251 1 935.00 935.00 CN
5 2251 1 450.00 450.00 CN
6 2251 1 25.00 25.00 CN
7 2251 2 701.38 1,402.76 CN
8 2251 1 179.57 179.57 CN
9 2251 2 50.00 100.00 CN
10 2251 14 100.00 1,400.00 CN
11 2251 1 1800.00 1,800.00 TW
12 2251 1 100.00 100.00 TW
13 2251 1 25.00 25.00 TW
14 2251 1 56.45 56.45 TW
15 2251 1 435.00 435.00 TW
16 2251 1 235.20 235.20 TW
17 2251 1 538.00 538.00 TW
18 2251 3 174.45 523.35 TW
19 2251 1 1923.00 1,923.00 TW
20 2251 1 843.00 843.00 TW
21 2251 1 400.00 400.00 CN
22 2251 1 400.00 400.00 CN
23 2251 1 3434.00 3,434.00 CN
24 2251 1 942.89 942.89 CN
25 8925 1 225.00 225.00 CN
26 7234 2 1307.00 2,614.00 JP
27 8925 1 1395.00 1,395.00 TW
28 2251 1 632.70 632.70 TW
29 6102 1 8500.00 8,500.00 TW
30 7616 1 1822.00 1,822.00 TW

Values on the fly

Thank you, Max! This is definitely pointing the direction I want to go. I
have to admit--parsing this expression is a little above my Excel IQ. What I
need is for the index value (e.g. "A") to be the same for every row that
contains the same value in C2 and G2.

For example, if the value of A2 is "A" because the value of C2 "8502" AND
the value of G2 is "TW", then the index value of every other row in the
worksheet containing the same values in C2 AND G2 should also be "A".

"Max" wrote:

One idea ..

Assuming data as posted starts in row2 down,
col C = item#
col G = country abbrev/code

Put in A2:
=IF(COUNTA(C2,G2)<2,"",C2&"-"&G2&"
"&"Order#:"&TEXT(SUMPRODUCT((C$2:C2=C2)*(G$2:G2=G2 )),"0000"))
Copy down as far as required to generate a unique text string index which
serializes the orders by the desired item#-country code,
viz in A2 down will appear:

8502-TW Order#:0001
8943-TW Order#:0001
2251-CN Order#:0001
2251-CN Order#:0002
2251-CN Order#:0003
etc

Adapt the expression to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Cosmo" wrote:
I'm looking for a way to automatically assign an 'index value' to cells in
column "index". Starting with row one, I want to assign "A" based on the
contents
of the "item#" and "country" cells in the same row. And then, I want "A"
assigned to
every row containing the same values in those columns. And then, I want the
next different value (in this case, the 2nd row) to be assigned "B"...again
with every other row containing the same values in the "Item#" and "county"
columns to be assigned the index value of "B"....and so on and so on. Is
there a function that can be combined with a logic statement to automatically
assign values to all cells in the "index" column?

Index Order Item#
country
1 8502 1 4215.00 4,215.00 TW
2 8943 1 1802.00 1,802.00 TW
3 2251 1 188.00 188.00 CN
4 2251 1 935.00 935.00 CN
5 2251 1 450.00 450.00 CN
6 2251 1 25.00 25.00 CN
7 2251 2 701.38 1,402.76 CN
8 2251 1 179.57 179.57 CN
9 2251 2 50.00 100.00 CN
10 2251 14 100.00 1,400.00 CN
11 2251 1 1800.00 1,800.00 TW
12 2251 1 100.00 100.00 TW
13 2251 1 25.00 25.00 TW
14 2251 1 56.45 56.45 TW
15 2251 1 435.00 435.00 TW
16 2251 1 235.20 235.20 TW
17 2251 1 538.00 538.00 TW
18 2251 3 174.45 523.35 TW
19 2251 1 1923.00 1,923.00 TW
20 2251 1 843.00 843.00 TW
21 2251 1 400.00 400.00 CN
22 2251 1 400.00 400.00 CN
23 2251 1 3434.00 3,434.00 CN
24 2251 1 942.89 942.89 CN
25 8925 1 225.00 225.00 CN
26 7234 2 1307.00 2,614.00 JP
27 8925 1 1395.00 1,395.00 TW
28 2251 1 632.70 632.70 TW
29 6102 1 8500.00 8,500.00 TW
30 7616 1 1822.00 1,822.00 TW

Values on the fly

Let's start over with this simple play which should get you going ..
(I'm not sure that what you're after can be handled in a single formula)

Assuming data as posted starts in row2 down,
col C = item#
col G = country abbrev/code

In H2: =C2&"-"&G2
Copy H2 down. Select cols B to H, do a Data Sort, by col H (asc)

Then put
In I2: =IF(COUNTIF(H$2:H2,H2)=1,ROW(),"")
Copy down

In J2: =I2
In J3: =IF(I3="",J2,I3)
Copy J3 down, to "filldown from above"
Col J returns the uniques serialization (numeric) results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Cosmo" wrote:
Thank you, Max! This is definitely pointing the direction I want to go. I
have to admit--parsing this expression is a little above my Excel IQ. What I
need is for the index value (e.g. "A") to be the same for every row that
contains the same value in C2 and G2.

For example, if the value of A2 is "A" because the value of C2 "8502" AND
the value of G2 is "TW", then the index value of every other row in the
worksheet containing the same values in C2 AND G2 should also be "A".