Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) |