Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula HELLLPPP!
I did a foo-pa. I accidently deleted a previous thread of mine, so I am
hoping someone there can help me finish it. I have Sheet1 where I need the formulas and Report sheet is where the data is. I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F ,MATCH("*"&B20,'Report'!$F:$F,0)))) And the results are this: 115-680G nb 02339660-9 3m 02339661-7 6m 02339662-5 9m 02339663-3 However, I need to have this occur for multiple Style numbers (ie 115-680G). This formula works for the above, but nowhere else. I want it to look at the Style number and then search for the text (NB) and then grab the Item Number. Can someone tell me how to get this main formula to work for all? 115-680G 115-681P 115-682W 115-683LC nb 02339660-9 nb nb nb 3m 02339661-7 3m 3m 3m 6m 02339662-5 6m 6m 6m 9m 02339663-3 9m 9m 9m This is my data: Style# Item Description Item# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Roger was helping me, but since I accidently deleted the post, I cannot get him to help further. Thanks, BeckyB |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula HELLLPPP!
It's not clear what you're wanting to do.
Do you want to lookup a style: 115-680G, and then return all the part numbers for that style: 02339660-9 02339661-7 02339662-5 02339663-3 I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$ F,MATCH("*"&B20,'Report'!$F:$F,0)))) So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's in B20? Is your data sorted or grouped together as is shown in the sample data you posted? Does every style have 4 part numbers as is shown in the sample data? -- Biff Microsoft Excel MVP "BeckyB" wrote in message ... I did a foo-pa. I accidently deleted a previous thread of mine, so I am hoping someone there can help me finish it. I have Sheet1 where I need the formulas and Report sheet is where the data is. I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F ,MATCH("*"&B20,'Report'!$F:$F,0)))) And the results are this: 115-680G nb 02339660-9 3m 02339661-7 6m 02339662-5 9m 02339663-3 However, I need to have this occur for multiple Style numbers (ie 115-680G). This formula works for the above, but nowhere else. I want it to look at the Style number and then search for the text (NB) and then grab the Item Number. Can someone tell me how to get this main formula to work for all? 115-680G 115-681P 115-682W 115-683LC nb 02339660-9 nb nb nb 3m 02339661-7 3m 3m 3m 6m 02339662-5 6m 6m 6m 9m 02339663-3 9m 9m 9m This is my data: Style# Item Description Item# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Roger was helping me, but since I accidently deleted the post, I cannot get him to help further. Thanks, BeckyB |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula HELLLPPP!
Thanks for trying to help!
Yes, every style has 4 item numbers associated with it. I want it to lookup the Style number, then refer to the item size (NB) in the text description to pull the part number. I am not sure what the different parts of the formula are since it was provided to me by someone else. $B$18 is where I have the Style number 115-680G on Sheet1. B20 is where the 1st size is displayed (NB) for that style. These locations will change for each style. (B18) 115-680G (B19) empty cell (B20) nb 02339660-9 (B21) 3m 02339661-7 (B22) 6m 02339662-5 (B23) 9m 02339663-3 Do you want to lookup a style: 115-680G, and then return all the part numbers for that style: YES So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's in B20? DON'T KNOW WHAT WILDCARD IS, SOMEONE ELSE WROTE FORMULA. B18 IS THE STYLE NUMBER & B20 IS THE SIZE TO LOOKUP THE PART NUMBER FOR. Is your data sorted or grouped together as is shown in the sample data you posted? DATA IS THOSE SIZES, BUT DIFFERENT STYLE NUMBERS. EACH DISPLAYED AS THIS ON SHEET1: 115-681P nb 3m 6m 9m 115-682W nb 3m 6m 9m DISPLAYED AS THIS IN REPORT TAB: Style# Item Description Part# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Does every style have 4 part numbers as is shown in the sample data? YES "T. Valko" wrote: It's not clear what you're wanting to do. Do you want to lookup a style: 115-680G, and then return all the part numbers for that style: 02339660-9 02339661-7 02339662-5 02339663-3 I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$ F,MATCH("*"&B20,'Report'!$F:$F,0)))) So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's in B20? Is your data sorted or grouped together as is shown in the sample data you posted? Does every style have 4 part numbers as is shown in the sample data? -- Biff Microsoft Excel MVP "BeckyB" wrote in message ... I did a foo-pa. I accidently deleted a previous thread of mine, so I am hoping someone there can help me finish it. I have Sheet1 where I need the formulas and Report sheet is where the data is. I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F ,MATCH("*"&B20,'Report'!$F:$F,0)))) And the results are this: 115-680G nb 02339660-9 3m 02339661-7 6m 02339662-5 9m 02339663-3 However, I need to have this occur for multiple Style numbers (ie 115-680G). This formula works for the above, but nowhere else. I want it to look at the Style number and then search for the text (NB) and then grab the Item Number. Can someone tell me how to get this main formula to work for all? 115-680G 115-681P 115-682W 115-683LC nb 02339660-9 nb nb nb 3m 02339661-7 3m 3m 3m 6m 02339662-5 6m 6m 6m 9m 02339663-3 9m 9m 9m This is my data: Style# Item Description Item# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Roger was helping me, but since I accidently deleted the post, I cannot get him to help further. Thanks, BeckyB . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula HELLLPPP!
Ok, not sure how your data is setup but see if this sample file helps.
zBeckyB.xls 15kb http://cjoint.com/?efve3nu2CJ -- Biff Microsoft Excel MVP "BeckyB" wrote in message ... Thanks for trying to help! Yes, every style has 4 item numbers associated with it. I want it to lookup the Style number, then refer to the item size (NB) in the text description to pull the part number. I am not sure what the different parts of the formula are since it was provided to me by someone else. $B$18 is where I have the Style number 115-680G on Sheet1. B20 is where the 1st size is displayed (NB) for that style. These locations will change for each style. (B18) 115-680G (B19) empty cell (B20) nb 02339660-9 (B21) 3m 02339661-7 (B22) 6m 02339662-5 (B23) 9m 02339663-3 Do you want to lookup a style: 115-680G, and then return all the part numbers for that style: YES So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's in B20? DON'T KNOW WHAT WILDCARD IS, SOMEONE ELSE WROTE FORMULA. B18 IS THE STYLE NUMBER & B20 IS THE SIZE TO LOOKUP THE PART NUMBER FOR. Is your data sorted or grouped together as is shown in the sample data you posted? DATA IS THOSE SIZES, BUT DIFFERENT STYLE NUMBERS. EACH DISPLAYED AS THIS ON SHEET1: 115-681P nb 3m 6m 9m 115-682W nb 3m 6m 9m DISPLAYED AS THIS IN REPORT TAB: Style# Item Description Part# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Does every style have 4 part numbers as is shown in the sample data? YES "T. Valko" wrote: It's not clear what you're wanting to do. Do you want to lookup a style: 115-680G, and then return all the part numbers for that style: 02339660-9 02339661-7 02339662-5 02339663-3 I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$ F,MATCH("*"&B20,'Report'!$F:$F,0)))) So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's in B20? Is your data sorted or grouped together as is shown in the sample data you posted? Does every style have 4 part numbers as is shown in the sample data? -- Biff Microsoft Excel MVP "BeckyB" wrote in message ... I did a foo-pa. I accidently deleted a previous thread of mine, so I am hoping someone there can help me finish it. I have Sheet1 where I need the formulas and Report sheet is where the data is. I have this formula: =LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F ,MATCH("*"&B20,'Report'!$F:$F,0)))) And the results are this: 115-680G nb 02339660-9 3m 02339661-7 6m 02339662-5 9m 02339663-3 However, I need to have this occur for multiple Style numbers (ie 115-680G). This formula works for the above, but nowhere else. I want it to look at the Style number and then search for the text (NB) and then grab the Item Number. Can someone tell me how to get this main formula to work for all? 115-680G 115-681P 115-682W 115-683LC nb 02339660-9 nb nb nb 3m 02339661-7 3m 3m 3m 6m 02339662-5 6m 6m 6m 9m 02339663-3 9m 9m 9m This is my data: Style# Item Description Item# 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 115-684A TRY FOOTSI SNP AZURE NB 02339676-5 115-684A TRY FOOTSI SNP AZURE 3M 02339677-3 115-684A TRY FOOTSI SNP AZURE 6M 02339678-1 115-684A TRY FOOTSI SNP AZURE 9M 02339680-7 Roger was helping me, but since I accidently deleted the post, I cannot get him to help further. Thanks, BeckyB . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Helllppp!! Sumproduct | Excel Discussion (Misc queries) |