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

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

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.

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

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

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

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

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