Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Hello,
I'm hoping that someone can help me with this problem. I have a spreadsheet where I am dumping raw data into one worksheet, and I want another 2 worksheets to look at that data and list out all of the information pertaining to their criteria. the Data worksheet (which is the suspense) contains sequence numbers, dates, vendor ID's and a host of other information. In column A, I have plucked out the first letter of the Vendor ID. (what I am trying to achieve - the first worksheet lets call it "first" look up all records that begin with an "A"or "B" or "C" or "D" up to "I" and list the records one by one down the page WITHOUT DUPLICATION, then the second worksheet lets call it "second" will list all of the remaining alphabet's "J" to "Z". I have managed to get part of the way there through this formula: =IF(ISERROR(INDEX(Suspense!$A$2:$K$139,SMALL(IF(Su spense!$A$2:$A$139="A",ROW(Suspense!$A$2:$A$139)), ROW(Suspense!1:1)),2)),"",(INDEX(Suspense!$A$2:$K$ 139,SMALL(IF(Suspense!$A$2:$A$139="A",ROW(Suspense !$A$2:$A$139)),ROW(Suspense!1:1)),2))) However I can only look at one criteria at a time, i.e. "A" first then "B". I would really like to get the formula to work right straight away with no messing around. So.... by using the formula above, I have split my criteria into lines of 100. All the "A"'s are looked up in rows 2-101, "B"'s are rows 102-202 etc. If there is no way of completing this formula in one go, is it possible to Look up the results on "first" and reference them on a new worksheet, where all information is copied across, and all blanks are skipped? Any help would be great!!! Thanks Excel_OZ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
This would be easier if you didn't "pluck out the first letter of the Vendor
ID". The "key" column should be the first column of your data table. We can extract the the values of the key column in alphabetical order and then use vlookups to extract the remaining columns of data. WITHOUT DUPLICATION Does that mean there are duplicate records or is that a reference to a "technical glitch" of a formula that has difficulty "finding" the correct data? Biff "Excel_Oz" wrote in message s.com... Hello, I'm hoping that someone can help me with this problem. I have a spreadsheet where I am dumping raw data into one worksheet, and I want another 2 worksheets to look at that data and list out all of the information pertaining to their criteria. the Data worksheet (which is the suspense) contains sequence numbers, dates, vendor ID's and a host of other information. In column A, I have plucked out the first letter of the Vendor ID. (what I am trying to achieve - the first worksheet lets call it "first" look up all records that begin with an "A"or "B" or "C" or "D" up to "I" and list the records one by one down the page WITHOUT DUPLICATION, then the second worksheet lets call it "second" will list all of the remaining alphabet's "J" to "Z". I have managed to get part of the way there through this formula: =IF(ISERROR(INDEX(Suspense!$A$2:$K$139,SMALL(IF(Su spense!$A$2:$A$139="A",ROW(Suspense!$A$2:$A$139)), ROW(Suspense!1:1)),2)),"",(INDEX(Suspense!$A$2:$K$ 139,SMALL(IF(Suspense!$A$2:$A$139="A",ROW(Suspense !$A$2:$A$139)),ROW(Suspense!1:1)),2))) However I can only look at one criteria at a time, i.e. "A" first then "B". I would really like to get the formula to work right straight away with no messing around. So.... by using the formula above, I have split my criteria into lines of 100. All the "A"'s are looked up in rows 2-101, "B"'s are rows 102-202 etc. If there is no way of completing this formula in one go, is it possible to Look up the results on "first" and reference them on a new worksheet, where all information is copied across, and all blanks are skipped? Any help would be great!!! Thanks Excel_OZ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Because we are sorting the information into 2 distinct alphabetical
piles, we need to use Vendor ID's which are Alphabetical. These Vendor ID's are listed within the raw data more than once - which means there is more than 1 transaction outstanding for that vendor. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Here's a sample file:
sample_sort.xls 22kb http://cjoint.com/?mog20AYNQj Biff "T. Valko" wrote in message ... This would be easier if you didn't "pluck out the first letter of the Vendor ID". The "key" column should be the first column of your data table. We can extract the the values of the key column in alphabetical order and then use vlookups to extract the remaining columns of data. WITHOUT DUPLICATION Does that mean there are duplicate records or is that a reference to a "technical glitch" of a formula that has difficulty "finding" the correct data? Biff "Excel_Oz" wrote in message s.com... Hello, I'm hoping that someone can help me with this problem. I have a spreadsheet where I am dumping raw data into one worksheet, and I want another 2 worksheets to look at that data and list out all of the information pertaining to their criteria. the Data worksheet (which is the suspense) contains sequence numbers, dates, vendor ID's and a host of other information. In column A, I have plucked out the first letter of the Vendor ID. (what I am trying to achieve - the first worksheet lets call it "first" look up all records that begin with an "A"or "B" or "C" or "D" up to "I" and list the records one by one down the page WITHOUT DUPLICATION, then the second worksheet lets call it "second" will list all of the remaining alphabet's "J" to "Z". I have managed to get part of the way there through this formula: =IF(ISERROR(INDEX(Suspense!$A$2:$K$139,SMALL(IF(Su spense!$A$2:$A$139="A",ROW(Suspense!$A$2:$A$139)), ROW(Suspense!1:1)),2)),"",(INDEX(Suspense!$A$2:$K$ 139,SMALL(IF(Suspense!$A$2:$A$139="A",ROW(Suspense !$A$2:$A$139)),ROW(Suspense!1:1)),2))) However I can only look at one criteria at a time, i.e. "A" first then "B". I would really like to get the formula to work right straight away with no messing around. So.... by using the formula above, I have split my criteria into lines of 100. All the "A"'s are looked up in rows 2-101, "B"'s are rows 102-202 etc. If there is no way of completing this formula in one go, is it possible to Look up the results on "first" and reference them on a new worksheet, where all information is copied across, and all blanks are skipped? Any help would be great!!! Thanks Excel_OZ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Thats Awesome!
And it works when there are multiples of the same vendor ID. My only question now is, how do I then formulate the other columns B to D to pick up the data pertaining to the info in column A without duplication? A simple Vlookup only finds and references the first instance of the given criteria. Thanks Excel_Oz |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Here's an updated file that accounts for multiple instances:
sample_sort(2).xls 26kb http://cjoint.com/?mpdX7y67UF Biff "Excel_Oz" wrote in message ups.com... Thats Awesome! And it works when there are multiples of the same vendor ID. My only question now is, how do I then formulate the other columns B to D to pick up the data pertaining to the info in column A without duplication? A simple Vlookup only finds and references the first instance of the given criteria. Thanks Excel_Oz |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
Legend!!!
Thanks for the help. I got the formula's now... all I need to do now is to understand the application of the formulas so I can learn from and use them more frequently. Thanks Excel_Oz |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and Reference (without duplication)
You're welcome. Thanks for the feedback!
Biff "Excel_Oz" wrote in message oups.com... Legend!!! Thanks for the help. I got the formula's now... all I need to do now is to understand the application of the formulas so I can learn from and use them more frequently. Thanks Excel_Oz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you lookup a value & get the cell reference? | Excel Worksheet Functions | |||
Lookup a value and return its cell reference instead of the value | Excel Discussion (Misc queries) | |||
Lookup and Reference | Excel Worksheet Functions | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |