software

Hello All,
I am merging two work sheets

the first sheet contains 80 rows and the second sheet contains 194 rows and
some of these rows are duplicated in sheet 2.

I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2.
So far the lookup function works fine.
However, if a row in sheet 1 is missing in sheet 2 i get the next larger
value and i don't want that.
when I further resarched it I found the following note about lookup function.

quot;If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value. quot;

1)I need help with the following:
is there any other function that i can use to return 0 if the value in sheet
1 is not found in sheet2
2)How can I accomidate the duplicate values in sheet2 ...I do want these
values to be duplicated in my results too

thank you in advance


Try vlookup(A1:sheet2!A1:A100,1,False)

or better

=vlookup(A1:sheet2!A:A,1,False)

and the items flagged #N/A are your missing list

--
Student Wrote:
gt; Hello All,
gt; I am merging two work sheets
gt;
gt; the first sheet contains 80 rows and the second sheet contains 194 rows
gt; and
gt; some of these rows are duplicated in sheet 2.
gt;
gt; I am using Lookup to determine if the 80 rows from sheet 1 exist in
gt; sheet 2.
gt; So far the lookup function works fine.
gt; However, if a row in sheet 1 is missing in sheet 2 i get the next
gt; larger
gt; value and i don't want that.
gt; when I further resarched it I found the following note about lookup
gt; function.
gt;
gt; quot;If LOOKUP can't find the lookup_value, it matches the largest value
gt; in
gt; lookup_vector that is less than or equal to lookup_value. quot;
gt;
gt; 1)I need help with the following:
gt; is there any other function that i can use to return 0 if the value in
gt; sheet
gt; 1 is not found in sheet2
gt; 2)How can I accomidate the duplicate values in sheet2 ...I do want
gt; these
gt; values to be duplicated in my results too
gt;
gt; thank you in advance--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534525Thank you for the fast respond that solved the matching problem
However, sheet2 is a table and upon finding the exact match for the column I
need to copy the entire row to sheet1
what function may i use for this
thank you again

quot;Bryan Hesseyquot; wrote:

gt;
gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt;
gt; or better
gt;
gt; =vlookup(A1:sheet2!A:A,1,False)
gt;
gt; and the items flagged #N/A are your missing list
gt;
gt; --
gt; Student Wrote:
gt; gt; Hello All,
gt; gt; I am merging two work sheets
gt; gt;
gt; gt; the first sheet contains 80 rows and the second sheet contains 194 rows
gt; gt; and
gt; gt; some of these rows are duplicated in sheet 2.
gt; gt;
gt; gt; I am using Lookup to determine if the 80 rows from sheet 1 exist in
gt; gt; sheet 2.
gt; gt; So far the lookup function works fine.
gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the next
gt; gt; larger
gt; gt; value and i don't want that.
gt; gt; when I further resarched it I found the following note about lookup
gt; gt; function.
gt; gt;
gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest value
gt; gt; in
gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt;
gt; gt; 1)I need help with the following:
gt; gt; is there any other function that i can use to return 0 if the value in
gt; gt; sheet
gt; gt; 1 is not found in sheet2
gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do want
gt; gt; these
gt; gt; values to be duplicated in my results too
gt; gt;
gt; gt; thank you in advance
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=534525
gt;
gt;


Hi,

On Sheet2 after you have an *N/A list, select a cell in row 1, then
Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A
items.

Select all displayed rows (complete rows all together), COPY, and paste
into Sheet 1 after your present data.

HTH

--

Student Wrote:
gt; Thank you for the fast respond that solved the matching problem
gt; However, sheet2 is a table and upon finding the exact match for the
gt; column I
gt; need to copy the entire row to sheet1
gt; what function may i use for this
gt; thank you again
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt;
gt; gt; or better
gt; gt;
gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt;
gt; gt; and the items flagged #N/A are your missing list
gt; gt;
gt; gt; --
gt; gt; Student Wrote:
gt; gt; gt; Hello All,
gt; gt; gt; I am merging two work sheets
gt; gt; gt;
gt; gt; gt; the first sheet contains 80 rows and the second sheet contains 194
gt; rows
gt; gt; gt; and
gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt;
gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1 exist
gt; in
gt; gt; gt; sheet 2.
gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the next
gt; gt; gt; larger
gt; gt; gt; value and i don't want that.
gt; gt; gt; when I further resarched it I found the following note about
gt; lookup
gt; gt; gt; function.
gt; gt; gt;
gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest
gt; value
gt; gt; gt; in
gt; gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt; gt;
gt; gt; gt; 1)I need help with the following:
gt; gt; gt; is there any other function that i can use to return 0 if the value
gt; in
gt; gt; gt; sheet
gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do want
gt; gt; gt; these
gt; gt; gt; values to be duplicated in my results too
gt; gt; gt;
gt; gt; gt; thank you in advance
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534525
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534525Good Morning
I can't use the filter for the following resons:

sheet2 contains User2 (contains the list of 194 users), Type, Date, Time
columns
I inserted User1 column (contains the list of 80 users) and these are the
users that i want
The result column is where I applied the Vlookup formula

If I filter the result column and delete all the N/A the problem is that my
results are not displayed next to their row.
look at the exapmle below
user @Y118 is on row 5 and the result column is displayed on row 3. If I
filter NA from the result column i will be deleting row 5 which has the data
for user @Y118

1 uSER1 uSER2 TYPE RESULT DATE
TIME
2 @Y040@Y530BSYS #N/A2/8/20068:48:00
3 @Y118@Y042BSYS @Y1182/14/200613:51:00
4 @Y131@Y072BSYS #N/A1/20/200610:21:00
5 @Y133@Y118BSYS #N/A1/17/20067:13:00

what i really need is a function like the Vlookup function that will be done
on a new sheet and that will match the User1 and User2 and copy the entire
row of matched row of User2

Please Advice thank you I really appreciate the effort
quot;Bryan Hesseyquot; wrote:

gt;
gt; Hi,
gt;
gt; On Sheet2 after you have an *N/A list, select a cell in row 1, then
gt; Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A
gt; items.
gt;
gt; Select all displayed rows (complete rows all together), COPY, and paste
gt; into Sheet 1 after your present data.
gt;
gt; HTH
gt;
gt; --
gt;
gt; Student Wrote:
gt; gt; Thank you for the fast respond that solved the matching problem
gt; gt; However, sheet2 is a table and upon finding the exact match for the
gt; gt; column I
gt; gt; need to copy the entire row to sheet1
gt; gt; what function may i use for this
gt; gt; thank you again
gt; gt;
gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt; gt;
gt; gt; gt; or better
gt; gt; gt;
gt; gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt; gt;
gt; gt; gt; and the items flagged #N/A are your missing list
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Student Wrote:
gt; gt; gt; gt; Hello All,
gt; gt; gt; gt; I am merging two work sheets
gt; gt; gt; gt;
gt; gt; gt; gt; the first sheet contains 80 rows and the second sheet contains 194
gt; gt; rows
gt; gt; gt; gt; and
gt; gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt; gt;
gt; gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1 exist
gt; gt; in
gt; gt; gt; gt; sheet 2.
gt; gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the next
gt; gt; gt; gt; larger
gt; gt; gt; gt; value and i don't want that.
gt; gt; gt; gt; when I further resarched it I found the following note about
gt; gt; lookup
gt; gt; gt; gt; function.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest
gt; gt; value
gt; gt; gt; gt; in
gt; gt; gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt; gt; gt;
gt; gt; gt; gt; 1)I need help with the following:
gt; gt; gt; gt; is there any other function that i can use to return 0 if the value
gt; gt; in
gt; gt; gt; gt; sheet
gt; gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do want
gt; gt; gt; gt; these
gt; gt; gt; gt; values to be duplicated in my results too
gt; gt; gt; gt;
gt; gt; gt; gt; thank you in advance
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Bryan Hessey
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Bryan Hessey's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=534525
gt;
gt;


Confused about what you are trying to achieve or the reason for
inserting User 1 list as column A of sheet 2.

Which list do you want as a final?

Are there details on sheet 1 in the same column(s) as the detail you
want from sheet 2?

Do you want a duplicate line in sheet 1 for any matched user from sheet
2, or do you want the details duplicated on the same line in sheet 1?

The 114 extra on sheet 2, do you want these added to sheet 1 or
ignored?

----------

To produce duplicate info on the same lines in sheet 1, then from sheet
1 do the lookup for eachrequired column, ie
If you have data in columns A to T, then in columns AA to AT do lookups
from sheet 2 for increasing column numbers, up to:=IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),quot;quot;,VLO OKUP(A1,Sheet2!B:B,20,FALSE))
-----------

To produce duplicate lines in Sheet 1 for items in sheet 2, use column
AA in sheet 2 for the lookup, and lookup on column B to the range A:A
(ex sheet 1) with a test to leave blank if a match is not found. Filter
these and Copy lines to sheet 1.

=IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),quot;quot;,VLOOKUP(B1 ,A:A,1,FALSE))
-----------

Does this help?

-

Student Wrote:
gt; Good Morning
gt; I can't use the filter for the following resons:
gt;
gt; sheet2 contains User2 (contains the list of 194 users), Type, Date,
gt; Time
gt; columns
gt; I inserted User1 column (contains the list of 80 users) and these are
gt; the
gt; users that i want
gt; The result column is where I applied the Vlookup formula
gt;
gt; If I filter the result column and delete all the N/A the problem is
gt; that my
gt; results are not displayed next to their row.
gt; look at the exapmle below
gt; user @Y118 is on row 5 and the result column is displayed on row 3. If
gt; I
gt; filter NA from the result column i will be deleting row 5 which has the
gt; data
gt; for user @Y118
gt;
gt; 1 uSER1 uSER2 TYPE RESULT DATE
gt; TIME
gt; 2 @Y040@Y530BSYS #N/A2/8/20068:48:00
gt; 3 @Y118@Y042BSYS @Y1182/14/200613:51:00
gt; 4 @Y131@Y072BSYS #N/A1/20/200610:21:00
gt; 5 @Y133@Y118BSYS #N/A1/17/20067:13:00
gt;
gt; what i really need is a function like the Vlookup function that will be
gt; done
gt; on a new sheet and that will match the User1 and User2 and copy the
gt; entire
gt; row of matched row of User2
gt;
gt; Please Advice thank you I really appreciate the effort
gt;
gt;
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Hi,
gt; gt;
gt; gt; On Sheet2 after you have an *N/A list, select a cell in row 1, then
gt; gt; Data, Filter, Autofilter, and use the dropdown arrow to select all
gt; *N/A
gt; gt; items.
gt; gt;
gt; gt; Select all displayed rows (complete rows all together), COPY, and
gt; paste
gt; gt; into Sheet 1 after your present data.
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Student Wrote:
gt; gt; gt; Thank you for the fast respond that solved the matching problem
gt; gt; gt; However, sheet2 is a table and upon finding the exact match for
gt; the
gt; gt; gt; column I
gt; gt; gt; need to copy the entire row to sheet1
gt; gt; gt; what function may i use for this
gt; gt; gt; thank you again
gt; gt; gt;
gt; gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt; gt; gt;
gt; gt; gt; gt; or better
gt; gt; gt; gt;
gt; gt; gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt; gt; gt;
gt; gt; gt; gt; and the items flagged #N/A are your missing list
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Student Wrote:
gt; gt; gt; gt; gt; Hello All,
gt; gt; gt; gt; gt; I am merging two work sheets
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; the first sheet contains 80 rows and the second sheet contains
gt; 194
gt; gt; gt; rows
gt; gt; gt; gt; gt; and
gt; gt; gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1
gt; exist
gt; gt; gt; in
gt; gt; gt; gt; gt; sheet 2.
gt; gt; gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the
gt; next
gt; gt; gt; gt; gt; larger
gt; gt; gt; gt; gt; value and i don't want that.
gt; gt; gt; gt; gt; when I further resarched it I found the following note about
gt; gt; gt; lookup
gt; gt; gt; gt; gt; function.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest
gt; gt; gt; value
gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 1)I need help with the following:
gt; gt; gt; gt; gt; is there any other function that i can use to return 0 if the
gt; value
gt; gt; gt; in
gt; gt; gt; gt; gt; sheet
gt; gt; gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do
gt; want
gt; gt; gt; gt; gt; these
gt; gt; gt; gt; gt; values to be duplicated in my results too
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; thank you in advance
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534525
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534525Sorry for causing the confusing

Here is what I am trying to do:

I have a sheet that contains 194 users and their information; however, I
need to extract only 80 users and their information out of that list.

I have a column of 80 users

The final list should contain only the 80 users and their information.
How can we achieve that? Please advice
Thank youquot;Bryan Hesseyquot; wrote:

gt;
gt; Confused about what you are trying to achieve or the reason for
gt; inserting User 1 list as column A of sheet 2.
gt;
gt; Which list do you want as a final?
gt;
gt; Are there details on sheet 1 in the same column(s) as the detail you
gt; want from sheet 2?
gt;
gt; Do you want a duplicate line in sheet 1 for any matched user from sheet
gt; 2, or do you want the details duplicated on the same line in sheet 1?
gt;
gt; The 114 extra on sheet 2, do you want these added to sheet 1 or
gt; ignored?
gt;
gt; ----------
gt;
gt; To produce duplicate info on the same lines in sheet 1, then from sheet
gt; 1 do the lookup for eachrequired column, ie
gt; If you have data in columns A to T, then in columns AA to AT do lookups
gt; from sheet 2 for increasing column numbers, up to:
gt;
gt;
gt; =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),quot;quot;,VLO OKUP(A1,Sheet2!B:B,20,FALSE))
gt; -----------
gt;
gt; To produce duplicate lines in Sheet 1 for items in sheet 2, use column
gt; AA in sheet 2 for the lookup, and lookup on column B to the range A:A
gt; (ex sheet 1) with a test to leave blank if a match is not found. Filter
gt; these and Copy lines to sheet 1.
gt;
gt; =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),quot;quot;,VLOOKUP(B1 ,A:A,1,FALSE))
gt; -----------
gt;
gt; Does this help?
gt;
gt; -
gt;
gt; Student Wrote:
gt; gt; Good Morning
gt; gt; I can't use the filter for the following resons:
gt; gt;
gt; gt; sheet2 contains User2 (contains the list of 194 users), Type, Date,
gt; gt; Time
gt; gt; columns
gt; gt; I inserted User1 column (contains the list of 80 users) and these are
gt; gt; the
gt; gt; users that i want
gt; gt; The result column is where I applied the Vlookup formula
gt; gt;
gt; gt; If I filter the result column and delete all the N/A the problem is
gt; gt; that my
gt; gt; results are not displayed next to their row.
gt; gt; look at the exapmle below
gt; gt; user @Y118 is on row 5 and the result column is displayed on row 3. If
gt; gt; I
gt; gt; filter NA from the result column i will be deleting row 5 which has the
gt; gt; data
gt; gt; for user @Y118
gt; gt;
gt; gt; 1 uSER1 uSER2 TYPE RESULT DATE
gt; gt; TIME
gt; gt; 2 @Y040@Y530BSYS #N/A2/8/20068:48:00
gt; gt; 3 @Y118@Y042BSYS @Y1182/14/200613:51:00
gt; gt; 4 @Y131@Y072BSYS #N/A1/20/200610:21:00
gt; gt; 5 @Y133@Y118BSYS #N/A1/17/20067:13:00
gt; gt;
gt; gt; what i really need is a function like the Vlookup function that will be
gt; gt; done
gt; gt; on a new sheet and that will match the User1 and User2 and copy the
gt; gt; entire
gt; gt; row of matched row of User2
gt; gt;
gt; gt; Please Advice thank you I really appreciate the effort
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; On Sheet2 after you have an *N/A list, select a cell in row 1, then
gt; gt; gt; Data, Filter, Autofilter, and use the dropdown arrow to select all
gt; gt; *N/A
gt; gt; gt; items.
gt; gt; gt;
gt; gt; gt; Select all displayed rows (complete rows all together), COPY, and
gt; gt; paste
gt; gt; gt; into Sheet 1 after your present data.
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Student Wrote:
gt; gt; gt; gt; Thank you for the fast respond that solved the matching problem
gt; gt; gt; gt; However, sheet2 is a table and upon finding the exact match for
gt; gt; the
gt; gt; gt; gt; column I
gt; gt; gt; gt; need to copy the entire row to sheet1
gt; gt; gt; gt; what function may i use for this
gt; gt; gt; gt; thank you again
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; or better
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; and the items flagged #N/A are your missing list
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Student Wrote:
gt; gt; gt; gt; gt; gt; Hello All,
gt; gt; gt; gt; gt; gt; I am merging two work sheets
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; the first sheet contains 80 rows and the second sheet contains
gt; gt; 194
gt; gt; gt; gt; rows
gt; gt; gt; gt; gt; gt; and
gt; gt; gt; gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1
gt; gt; exist
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; sheet 2.
gt; gt; gt; gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the
gt; gt; next
gt; gt; gt; gt; gt; gt; larger
gt; gt; gt; gt; gt; gt; value and i don't want that.
gt; gt; gt; gt; gt; gt; when I further resarched it I found the following note about
gt; gt; gt; gt; lookup
gt; gt; gt; gt; gt; gt; function.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest
gt; gt; gt; gt; value
gt; gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; 1)I need help with the following:
gt; gt; gt; gt; gt; gt; is there any other function that i can use to return 0 if the
gt; gt; value
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; sheet
gt; gt; gt; gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do
gt; gt; want
gt; gt; gt; gt; gt; gt; these
gt; gt; gt; gt; gt; gt; values to be duplicated in my results too
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; thank you in advance
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Bryan Hessey
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Bryan Hessey's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=534525
gt;
gt;

Sorry to bother you, but i haven't got a solution yet ...does it mean this is
not doable with Excel

quot;Bryan Hesseyquot; wrote:

gt;
gt; Confused about what you are trying to achieve or the reason for
gt; inserting User 1 list as column A of sheet 2.
gt;
gt; Which list do you want as a final?
gt;
gt; Are there details on sheet 1 in the same column(s) as the detail you
gt; want from sheet 2?
gt;
gt; Do you want a duplicate line in sheet 1 for any matched user from sheet
gt; 2, or do you want the details duplicated on the same line in sheet 1?
gt;
gt; The 114 extra on sheet 2, do you want these added to sheet 1 or
gt; ignored?
gt;
gt; ----------
gt;
gt; To produce duplicate info on the same lines in sheet 1, then from sheet
gt; 1 do the lookup for eachrequired column, ie
gt; If you have data in columns A to T, then in columns AA to AT do lookups
gt; from sheet 2 for increasing column numbers, up to:
gt;
gt;
gt; =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),quot;quot;,VLO OKUP(A1,Sheet2!B:B,20,FALSE))
gt; -----------
gt;
gt; To produce duplicate lines in Sheet 1 for items in sheet 2, use column
gt; AA in sheet 2 for the lookup, and lookup on column B to the range A:A
gt; (ex sheet 1) with a test to leave blank if a match is not found. Filter
gt; these and Copy lines to sheet 1.
gt;
gt; =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),quot;quot;,VLOOKUP(B1 ,A:A,1,FALSE))
gt; -----------
gt;
gt; Does this help?
gt;
gt; -
gt;
gt; Student Wrote:
gt; gt; Good Morning
gt; gt; I can't use the filter for the following resons:
gt; gt;
gt; gt; sheet2 contains User2 (contains the list of 194 users), Type, Date,
gt; gt; Time
gt; gt; columns
gt; gt; I inserted User1 column (contains the list of 80 users) and these are
gt; gt; the
gt; gt; users that i want
gt; gt; The result column is where I applied the Vlookup formula
gt; gt;
gt; gt; If I filter the result column and delete all the N/A the problem is
gt; gt; that my
gt; gt; results are not displayed next to their row.
gt; gt; look at the exapmle below
gt; gt; user @Y118 is on row 5 and the result column is displayed on row 3. If
gt; gt; I
gt; gt; filter NA from the result column i will be deleting row 5 which has the
gt; gt; data
gt; gt; for user @Y118
gt; gt;
gt; gt; 1 uSER1 uSER2 TYPE RESULT DATE
gt; gt; TIME
gt; gt; 2 @Y040@Y530BSYS #N/A2/8/20068:48:00
gt; gt; 3 @Y118@Y042BSYS @Y1182/14/200613:51:00
gt; gt; 4 @Y131@Y072BSYS #N/A1/20/200610:21:00
gt; gt; 5 @Y133@Y118BSYS #N/A1/17/20067:13:00
gt; gt;
gt; gt; what i really need is a function like the Vlookup function that will be
gt; gt; done
gt; gt; on a new sheet and that will match the User1 and User2 and copy the
gt; gt; entire
gt; gt; row of matched row of User2
gt; gt;
gt; gt; Please Advice thank you I really appreciate the effort
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; On Sheet2 after you have an *N/A list, select a cell in row 1, then
gt; gt; gt; Data, Filter, Autofilter, and use the dropdown arrow to select all
gt; gt; *N/A
gt; gt; gt; items.
gt; gt; gt;
gt; gt; gt; Select all displayed rows (complete rows all together), COPY, and
gt; gt; paste
gt; gt; gt; into Sheet 1 after your present data.
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Student Wrote:
gt; gt; gt; gt; Thank you for the fast respond that solved the matching problem
gt; gt; gt; gt; However, sheet2 is a table and upon finding the exact match for
gt; gt; the
gt; gt; gt; gt; column I
gt; gt; gt; gt; need to copy the entire row to sheet1
gt; gt; gt; gt; what function may i use for this
gt; gt; gt; gt; thank you again
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; or better
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; and the items flagged #N/A are your missing list
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Student Wrote:
gt; gt; gt; gt; gt; gt; Hello All,
gt; gt; gt; gt; gt; gt; I am merging two work sheets
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; the first sheet contains 80 rows and the second sheet contains
gt; gt; 194
gt; gt; gt; gt; rows
gt; gt; gt; gt; gt; gt; and
gt; gt; gt; gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1
gt; gt; exist
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; sheet 2.
gt; gt; gt; gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get the
gt; gt; next
gt; gt; gt; gt; gt; gt; larger
gt; gt; gt; gt; gt; gt; value and i don't want that.
gt; gt; gt; gt; gt; gt; when I further resarched it I found the following note about
gt; gt; gt; gt; lookup
gt; gt; gt; gt; gt; gt; function.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the largest
gt; gt; gt; gt; value
gt; gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; lookup_vector that is less than or equal to lookup_value. quot;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; 1)I need help with the following:
gt; gt; gt; gt; gt; gt; is there any other function that i can use to return 0 if the
gt; gt; value
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; sheet
gt; gt; gt; gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I do
gt; gt; want
gt; gt; gt; gt; gt; gt; these
gt; gt; gt; gt; gt; gt; values to be duplicated in my results too
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; thank you in advance
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Bryan Hessey
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Bryan Hessey's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=534525
gt;
gt;


Copy the worksheet of 194 users, and on that sheet in a spare column, do
a lookup to the 80 user list,

=vlookup(thisUser,UserList1:Userlistend,1,false)

and 80 of those should be users, the remaining should be #N/A

Copy the column, and Paste Special = Values back over itsself.

Select All Data and sort over that column, delete all #N/A lines.

That should be your list.

If there were other details on the original 80 column sheet, they can
be picked up with additional VLoopkup into additional cells.

This should suit your purpose

--Student Wrote:
gt; Sorry to bother you, but i haven't got a solution yet ...does it mean
gt; this is
gt; not doable with Excel
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Confused about what you are trying to achieve or the reason for
gt; gt; inserting User 1 list as column A of sheet 2.
gt; gt;
gt; gt; Which list do you want as a final?
gt; gt;
gt; gt; Are there details on sheet 1 in the same column(s) as the detail you
gt; gt; want from sheet 2?
gt; gt;
gt; gt; Do you want a duplicate line in sheet 1 for any matched user from
gt; sheet
gt; gt; 2, or do you want the details duplicated on the same line in sheet
gt; 1?
gt; gt;
gt; gt; The 114 extra on sheet 2, do you want these added to sheet 1 or
gt; gt; ignored?
gt; gt;
gt; gt; ----------
gt; gt;
gt; gt; To produce duplicate info on the same lines in sheet 1, then from
gt; sheet
gt; gt; 1 do the lookup for eachrequired column, ie
gt; gt; If you have data in columns A to T, then in columns AA to AT do
gt; lookups
gt; gt; from sheet 2 for increasing column numbers, up to:
gt; gt;
gt; gt;
gt; gt;
gt; =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),quot;quot;,VLO OKUP(A1,Sheet2!B:B,20,FALSE))
gt; gt; -----------
gt; gt;
gt; gt; To produce duplicate lines in Sheet 1 for items in sheet 2, use
gt; column
gt; gt; AA in sheet 2 for the lookup, and lookup on column B to the range
gt; A:A
gt; gt; (ex sheet 1) with a test to leave blank if a match is not found.
gt; Filter
gt; gt; these and Copy lines to sheet 1.
gt; gt;
gt; gt; =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),quot;quot;,VLOOKUP(B1 ,A:A,1,FALSE))
gt; gt; -----------
gt; gt;
gt; gt; Does this help?
gt; gt;
gt; gt; -
gt; gt;
gt; gt; Student Wrote:
gt; gt; gt; Good Morning
gt; gt; gt; I can't use the filter for the following resons:
gt; gt; gt;
gt; gt; gt; sheet2 contains User2 (contains the list of 194 users), Type,
gt; Date,
gt; gt; gt; Time
gt; gt; gt; columns
gt; gt; gt; I inserted User1 column (contains the list of 80 users) and these
gt; are
gt; gt; gt; the
gt; gt; gt; users that i want
gt; gt; gt; The result column is where I applied the Vlookup formula
gt; gt; gt;
gt; gt; gt; If I filter the result column and delete all the N/A the problem
gt; is
gt; gt; gt; that my
gt; gt; gt; results are not displayed next to their row.
gt; gt; gt; look at the exapmle below
gt; gt; gt; user @Y118 is on row 5 and the result column is displayed on row 3.
gt; If
gt; gt; gt; I
gt; gt; gt; filter NA from the result column i will be deleting row 5 which has
gt; the
gt; gt; gt; data
gt; gt; gt; for user @Y118
gt; gt; gt;
gt; gt; gt; 1 uSER1 uSER2 TYPE RESULT
gt; DATE
gt; gt; gt; TIME
gt; gt; gt; 2 @Y040@Y530BSYS #N/A2/8/20068:48:00
gt; gt; gt; 3 @Y118@Y042BSYS
gt; @Y1182/14/200613:51:00
gt; gt; gt; 4 @Y131@Y072BSYS #N/A1/20/200610:21:00
gt; gt; gt; 5 @Y133@Y118BSYS #N/A1/17/20067:13:00
gt; gt; gt;
gt; gt; gt; what i really need is a function like the Vlookup function that
gt; will be
gt; gt; gt; done
gt; gt; gt; on a new sheet and that will match the User1 and User2 and copy
gt; the
gt; gt; gt; entire
gt; gt; gt; row of matched row of User2
gt; gt; gt;
gt; gt; gt; Please Advice thank you I really appreciate the effort
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Hi,
gt; gt; gt; gt;
gt; gt; gt; gt; On Sheet2 after you have an *N/A list, select a cell in row 1,
gt; then
gt; gt; gt; gt; Data, Filter, Autofilter, and use the dropdown arrow to select
gt; all
gt; gt; gt; *N/A
gt; gt; gt; gt; items.
gt; gt; gt; gt;
gt; gt; gt; gt; Select all displayed rows (complete rows all together), COPY,
gt; and
gt; gt; gt; paste
gt; gt; gt; gt; into Sheet 1 after your present data.
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Student Wrote:
gt; gt; gt; gt; gt; Thank you for the fast respond that solved the matching
gt; problem
gt; gt; gt; gt; gt; However, sheet2 is a table and upon finding the exact match
gt; for
gt; gt; gt; the
gt; gt; gt; gt; gt; column I
gt; gt; gt; gt; gt; need to copy the entire row to sheet1
gt; gt; gt; gt; gt; what function may i use for this
gt; gt; gt; gt; gt; thank you again
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Try vlookup(A1:sheet2!A1:A100,1,False)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; or better
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =vlookup(A1:sheet2!A:A,1,False)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; and the items flagged #N/A are your missing list
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; Student Wrote:
gt; gt; gt; gt; gt; gt; gt; Hello All,
gt; gt; gt; gt; gt; gt; gt; I am merging two work sheets
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; the first sheet contains 80 rows and the second sheet
gt; contains
gt; gt; gt; 194
gt; gt; gt; gt; gt; rows
gt; gt; gt; gt; gt; gt; gt; and
gt; gt; gt; gt; gt; gt; gt; some of these rows are duplicated in sheet 2.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I am using Lookup to determine if the 80 rows from sheet 1
gt; gt; gt; exist
gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; gt; sheet 2.
gt; gt; gt; gt; gt; gt; gt; So far the lookup function works fine.
gt; gt; gt; gt; gt; gt; gt; However, if a row in sheet 1 is missing in sheet 2 i get
gt; the
gt; gt; gt; next
gt; gt; gt; gt; gt; gt; gt; larger
gt; gt; gt; gt; gt; gt; gt; value and i don't want that.
gt; gt; gt; gt; gt; gt; gt; when I further resarched it I found the following note
gt; about
gt; gt; gt; gt; gt; lookup
gt; gt; gt; gt; gt; gt; gt; function.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;If LOOKUP can't find the lookup_value, it matches the
gt; largest
gt; gt; gt; gt; gt; value
gt; gt; gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; gt; lookup_vector that is less than or equal to lookup_value.
gt; quot;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; 1)I need help with the following:
gt; gt; gt; gt; gt; gt; gt; is there any other function that i can use to return 0 if
gt; the
gt; gt; gt; value
gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; gt; sheet
gt; gt; gt; gt; gt; gt; gt; 1 is not found in sheet2
gt; gt; gt; gt; gt; gt; gt; 2)How can I accomidate the duplicate values in sheet2 ...I
gt; do
gt; gt; gt; want
gt; gt; gt; gt; gt; gt; gt; these
gt; gt; gt; gt; gt; gt; gt; values to be duplicated in my results too
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; thank you in advance
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; gt; gt;
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=534525
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534525
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534525