| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show dates if multiple Serail numbers are equal
Hey, I have a list which lists failures of certain parts ( column G,PN in Failure sheet) inculding the machine (column B,SN .. Serial Number in Failure) and Install Date (Column D in Failure) some other dates. I also have a sheet "Built list" that includes all built Date for those SN .. Serial Numbers..but we dont care about that. I have no problem matching those. The main sheet (called Query1) includes column A: parts (PN) and coulmn B:Serial Numbers (SN) and Install Date (and other dates), mentioned above. I want excel to give me the install date from the Failure-sheet when the PN *and* SN from the Query1 sheet match the PN and SN in the Failure-sheet. For example: I assign the Install date to a Serial number by =INDEX(Failure!D:D,MATCH(Query1!$B2,Failure!B:B,0) ) But this ignores the PN and gives me a wrong date. I don't know how to match both. I would be very thankful for any help - I hope this was understandable.. I added the sheet,so you can play with it :) +-------------------------------------------------------------------+ |Filename: Example.zip | |Download: http://www.excelforum.com/attachment.php?postid=4604 | +-------------------------------------------------------------------+ -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
Is it normal/possible to have SN numbers that are the same as each other? Is it normal/possible to have PN numbers that are the same as each other? If either of them are a unique number each time it would be possible to do a vlookup and an IF formila. If yes, I can advise the code. James -- JamesArchibald ------------------------------------------------------------------------ JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
In J2 and copy down
=IF(B2=INDIRECT("Failure!B" &MATCH(A2,Failure!G:G,0)),INDIRECT("Failure!D"&MAT CH(A2,Failure!G:G,0)),"") If, as previous posting, PN/SN combinations are unique, then VLOOKUP could be used. "Dark_Templar" wrote: Hey, I have a list which lists failures of certain parts ( column G,PN in Failure sheet) inculding the machine (column B,SN .. Serial Number in Failure) and Install Date (Column D in Failure) some other dates. I also have a sheet "Built list" that includes all built Date for those SN .. Serial Numbers..but we dont care about that. I have no problem matching those. The main sheet (called Query1) includes column A: parts (PN) and coulmn B:Serial Numbers (SN) and Install Date (and other dates), mentioned above. I want excel to give me the install date from the Failure-sheet when the PN *and* SN from the Query1 sheet match the PN and SN in the Failure-sheet. For example: I assign the Install date to a Serial number by =INDEX(Failure!D:D,MATCH(Query1!$B2,Failure!B:B,0) ) But this ignores the PN and gives me a wrong date. I don't know how to match both. I would be very thankful for any help - I hope this was understandable.. I added the sheet,so you can play with it :) +-------------------------------------------------------------------+ |Filename: Example.zip | |Download: http://www.excelforum.com/attachment.php?postid=4604 | +-------------------------------------------------------------------+ -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
Thanks for the quick response. A SN can have multiple PNs A PN can have mutiple SNs Unfortunately excel says the function =IF(B2=INDIRECT("Failure!B"&MATCH(A2,Failure!G:G,0 )),INDIRECT("Failure!D"&MATCH(A2,Failure!G:G,0))," ") contains an error,at the marked spot. To me it looks ok tho... Whats wrong? :( -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
Yeah that is fine, but can there be two SN's that are the same number in column B in the Failure sheet. Can there be two PN's that are the same number in column G of the Failure sheet? -- JamesArchibald ------------------------------------------------------------------------ JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
Can't see what's wrong and it worked for me. HOWEVER, I realised it will only
work if a PN occurs only once in Column G (it was late at night when I did it!) so we need another solution. You need to match PN/SN pairs to get the date; one way is to add a "helper" column of PN & SN concatenated in your "Failure" sheet and do a VLOOKUP. Adding a helper column C in FAILURE(shifting everything right) contain =h2 & b2 i.e. PN+SN ,copied down, then in colum I of Query1 we put: =VLOOKUP(A2&B2,Failure!C:E,3,FALSE) and copy down. HTH "Dark_Templar" wrote: Thanks for the quick response. A SN can have multiple PNs A PN can have mutiple SNs Unfortunately excel says the function =IF(B2=INDIRECT("Failure!B"&MATCH(A2,Failure!G:G,0 )),INDIRECT("Failure!D"&MATCH(A2,Failure!G:G,0))," ") contains an error,at the marked spot. To me it looks ok tho... Whats wrong? :( -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show dates if multiple Serail numbers are equal
A second (or is it third) thought:
Make column A the helper, and change VLOOKUP to =VLOOKUP(A2&B2,Failure!A:E,5,FALSE) "Toppers" wrote: Can't see what's wrong and it worked for me. HOWEVER, I realised it will only work if a PN occurs only once in Column G (it was late at night when I did it!) so we need another solution. You need to match PN/SN pairs to get the date; one way is to add a "helper" column of PN & SN concatenated in your "Failure" sheet and do a VLOOKUP. Adding a helper column C in FAILURE(shifting everything right) contain =h2 & b2 i.e. PN+SN ,copied down, then in colum I of Query1 we put: =VLOOKUP(A2&B2,Failure!C:E,3,FALSE) and copy down. HTH "Dark_Templar" wrote: Thanks for the quick response. A SN can have multiple PNs A PN can have mutiple SNs Unfortunately excel says the function =IF(B2=INDIRECT("Failure!B"&MATCH(A2,Failure!G:G,0 )),INDIRECT("Failure!D"&MATCH(A2,Failure!G:G,0))," ") contains an error,at the marked spot. To me it looks ok tho... Whats wrong? :( -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=531092 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
| How do I show break in bar graph to show large and small numbers | Charts and Charting in Excel | |||
| Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) | |||
| Excel: Which numbers in a column equal a certain number (withou. | Excel Worksheet Functions | |||
| COUNTIF With Multiple Dates, Columns and Text | Excel Worksheet Functions | |||
Hybrid Mode
