| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formula or macro
I am trying to compare two columns in two different worksheets. If the cell
in column model and serial worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. Help please. Worksheet #1 A B C D Model Serial 801 Beaumont 7194629 E32696R36 C2405 PSPSV05008 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347 Worksheet #2 Model # Serial # 32HL67 AM339004740 32HL67U AM379011491 32HL67U AM37010883 32HL67U AM37021155 32HL67U AM37011550 32HL67U AM379011448 32LC7D 708MXTC4Y105 32LC7D 706MCMT0M499 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula or macro
I would suggest that in Sheet1 you join the model and serial columns
together in a helper column, eg. put this in G2: =E2&F2 and copy down. Then in your new sheet you could have a MATCH formula along these lines in A2: =MATCH(Sheet2!A2&Sheet2!B2,Sheet1!G$2:G$1000,0) to find an exact match, and then this formula in B2: =IF(ISNA($A2),"",INDEX(Sheet1!A$2:A$1000,$A2,0)) Copy B2 into C2:E2 and then copy A2:E2 down for as many entries as you have in Sheet2. Hope this helps. Pete On Jan 29, 7:36*pm, pm wrote: I am trying to compare two columns in two different worksheets. *If the cell in column model and serial *worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. *Help please. * Worksheet #1 A * * B * * * * * * C * * * * * D * * * * * * * * * * *Model * * * * * Serial 801 Beaumont 7194629 E32696R36 * * * C2405 * * *PSPSV05008 805 Houston * *7764192 E-062306CRR * TM233XC * *LXT21051803284 801 Beaumont 7618786 E32215R24 * * * TM243XC * *LXT3005347347 Worksheet #2 Model # Serial # 32HL67 * * * * * * * * *AM339004740 * * * * 32HL67U * * * * * * * * AM379011491 * * * * 32HL67U * * * * * * * * AM37010883 * * * * * 32HL67U * * * * * * * * AM37021155 * * * * * 32HL67U * * * * * * * * AM37011550 * * * * * 32HL67U * * * * * * * * AM379011448 * * * * 32LC7D * * * * * * * * *708MXTC4Y105 * * * * 32LC7D * * * * * * * * *706MCMT0M499 * * * * |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula or macro
Hi Pete,
Thanks for the suggestion. I will try it. "Pete_UK" wrote: I would suggest that in Sheet1 you join the model and serial columns together in a helper column, eg. put this in G2: =E2&F2 and copy down. Then in your new sheet you could have a MATCH formula along these lines in A2: =MATCH(Sheet2!A2&Sheet2!B2,Sheet1!G$2:G$1000,0) to find an exact match, and then this formula in B2: =IF(ISNA($A2),"",INDEX(Sheet1!A$2:A$1000,$A2,0)) Copy B2 into C2:E2 and then copy A2:E2 down for as many entries as you have in Sheet2. Hope this helps. Pete On Jan 29, 7:36 pm, pm wrote: I am trying to compare two columns in two different worksheets. If the cell in column model and serial worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. Help please. Worksheet #1 A B C D Model Serial 801 Beaumont 7194629 E32696R36 C2405 PSPSV05008 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347 Worksheet #2 Model # Serial # 32HL67 AM339004740 32HL67U AM379011491 32HL67U AM37010883 32HL67U AM37021155 32HL67U AM37011550 32HL67U AM379011448 32LC7D 708MXTC4Y105 32LC7D 706MCMT0M499 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula or macro
Pete,
I'm having difficulty getting this to work...any other suggestions? "Pete_UK" wrote: I would suggest that in Sheet1 you join the model and serial columns together in a helper column, eg. put this in G2: =E2&F2 and copy down. Then in your new sheet you could have a MATCH formula along these lines in A2: =MATCH(Sheet2!A2&Sheet2!B2,Sheet1!G$2:G$1000,0) to find an exact match, and then this formula in B2: =IF(ISNA($A2),"",INDEX(Sheet1!A$2:A$1000,$A2,0)) Copy B2 into C2:E2 and then copy A2:E2 down for as many entries as you have in Sheet2. Hope this helps. Pete On Jan 29, 7:36 pm, pm wrote: I am trying to compare two columns in two different worksheets. If the cell in column model and serial worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. Help please. Worksheet #1 A B C D Model Serial 801 Beaumont 7194629 E32696R36 C2405 PSPSV05008 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347 Worksheet #2 Model # Serial # 32HL67 AM339004740 32HL67U AM379011491 32HL67U AM37010883 32HL67U AM37021155 32HL67U AM37011550 32HL67U AM379011448 32LC7D 708MXTC4Y105 32LC7D 706MCMT0M499 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula or macro
Firstly, you need to resort and organize your Worksheet #2 table as follows
(I changed some of the values so you can compare results, later and follows - ensure the Model # column is sorted in ascending order): Worksheet 1 Model # Serial # A B C D 32HL67 AM339004740 801 Beaumont 7194629 E32696R36 32HL67U AM379011491 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 For reference purposes, the 32HL67 would be in cell A6. The following would be your workhsheet #2 table (I did everything on 1 worksheet): Worksheet 2 Imported Data Model # Serial # A B C D 32HL67 AM339004740 801 Beaumont 7194629 E32696R36 32HL67U AM379011491 805 Houston 7764192 E-062306CRR 32HL67U AM37010883 Here 32HL67 would be in cell A14. The column A formula in worksheet #2 would be as follows: =IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A1 4,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,3),"") Looks bad, but actually easy. The logical test for the IF statement contains the logical AND function which return true if all of its arguements are true. The AND function compares the Model # and Serial # to see if both are the same. If they are, then it returns, in the above formula, the A column value - if not, it returns a blank (the ""). The column B formula would be: =IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A1 4,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,4),"") Same thing except the last VLOOKUP fetches the column 4 value (the column B value). I can send you the sample worksheet if you like. -- Gnothi se auton. "pm" wrote: I am trying to compare two columns in two different worksheets. If the cell in column model and serial worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. Help please. Worksheet #1 A B C D Model Serial 801 Beaumont 7194629 E32696R36 C2405 PSPSV05008 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347 Worksheet #2 Model # Serial # 32HL67 AM339004740 32HL67U AM379011491 32HL67U AM37010883 32HL67U AM37021155 32HL67U AM37011550 32HL67U AM379011448 32LC7D 708MXTC4Y105 32LC7D 706MCMT0M499 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula or macro
Yes, would you please send me a sample? thank you so very much.....
"rsantaro" wrote: Firstly, you need to resort and organize your Worksheet #2 table as follows (I changed some of the values so you can compare results, later and follows - ensure the Model # column is sorted in ascending order): Worksheet 1 Model # Serial # A B C D 32HL67 AM339004740 801 Beaumont 7194629 E32696R36 32HL67U AM379011491 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 For reference purposes, the 32HL67 would be in cell A6. The following would be your workhsheet #2 table (I did everything on 1 worksheet): Worksheet 2 Imported Data Model # Serial # A B C D 32HL67 AM339004740 801 Beaumont 7194629 E32696R36 32HL67U AM379011491 805 Houston 7764192 E-062306CRR 32HL67U AM37010883 Here 32HL67 would be in cell A14. The column A formula in worksheet #2 would be as follows: =IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A1 4,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,3),"") Looks bad, but actually easy. The logical test for the IF statement contains the logical AND function which return true if all of its arguements are true. The AND function compares the Model # and Serial # to see if both are the same. If they are, then it returns, in the above formula, the A column value - if not, it returns a blank (the ""). The column B formula would be: =IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A1 4,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,4),"") Same thing except the last VLOOKUP fetches the column 4 value (the column B value). I can send you the sample worksheet if you like. -- Gnothi se auton. "pm" wrote: I am trying to compare two columns in two different worksheets. If the cell in column model and serial worksheet #2 matches the cell in column Model and serial worksheet #1, then I want to pull data from column A, B, C, and D from worksheet #1 into a separate sheet. Help please. Worksheet #1 A B C D Model Serial 801 Beaumont 7194629 E32696R36 C2405 PSPSV05008 805 Houston 7764192 E-062306CRR TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347 Worksheet #2 Model # Serial # 32HL67 AM339004740 32HL67U AM379011491 32HL67U AM37010883 32HL67U AM37021155 32HL67U AM37011550 32HL67U AM379011448 32LC7D 708MXTC4Y105 32LC7D 706MCMT0M499 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Macro | Excel Discussion (Misc queries) | |||
| Running a macro from a conditional formula | Excel Discussion (Misc queries) | |||
| [B]Conditional Macro?[/B] | Excel Discussion (Misc queries) | |||
| conditional sum and macro | Excel Discussion (Misc queries) | |||
| Conditional Macro | Excel Worksheet Functions | |||
Linear Mode
