Look up a value from a row after verifying two other values
I have a spreadsheet that I am populating with miles from point to point.
Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? |
Look up a value from a row after verifying two other values
Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49*pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? |
Look up a value from a row after verifying two other values
Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
Pete, Thanks for the idea. It is a good one. The problem is, I have to have a
programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
Can you post a small sample of the data that demonstrates what you're
looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
I will try, The spreadsheet is at work and i am home now.
store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
sorry, the spaces got removed.
It is a list of stores in an area and I need the distance from each store to each other store. The sheet will naturally have each set listed twice, store 1 to store 2 and store 2 to store 1 ect, I would like the second set to auto populate after I enter the miles the first time. "Paul W" wrote: I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
OK, that helps...but it's not entirely clear!
So, based on that sample my best guess for the first result is 485 but I don't see a result for the second one. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
Yes, I think you get the idea. The sheets are 200 to 1300 rows long so the
result to the 2nd one would be below somewhere. The idea would be to paste the formula into the whole sheet and start filling in the miles and as you go each "second set" would auto populate and you would finish in half the time. "T. Valko" wrote: OK, that helps...but it's not entirely clear! So, based on that sample my best guess for the first result is 485 but I don't see a result for the second one. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
Assume this data is in the range A2:E4 -
Comma delimited columns store,address,2nd store,address,Miles #1,1 1st st or,#2,23 2nd st CA,485 #1,1 1st st or,#3,48 a st ca,385 #1,1 1st st or,#4,65 r st ca,426 To lookup: #2, 23 2nd st ca, #1,1 1st st or A5 = #2 B5 = 23 2nd st ca C5 = #1 D5 = 1 1st st or =SUMPRODUCT(--(A2:A4=C5),--(B2:B4=D5),--(C2:C4=A5),--(D2:D4=B5),E2:E4) -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Yes, I think you get the idea. The sheets are 200 to 1300 rows long so the result to the 2nd one would be below somewhere. The idea would be to paste the formula into the whole sheet and start filling in the miles and as you go each "second set" would auto populate and you would finish in half the time. "T. Valko" wrote: OK, that helps...but it's not entirely clear! So, based on that sample my best guess for the first result is 485 but I don't see a result for the second one. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
I do not think there is a way to accomplish what I am looking for. This
formula below returned a circular reference error. Even the idea behind it though would work only if I were building the spreadsheet from scratch and not just populating the "miles" field. Thank you for your help. "T. Valko" wrote: Assume this data is in the range A2:E4 - Comma delimited columns store,address,2nd store,address,Miles #1,1 1st st or,#2,23 2nd st CA,485 #1,1 1st st or,#3,48 a st ca,385 #1,1 1st st or,#4,65 r st ca,426 To lookup: #2, 23 2nd st ca, #1,1 1st st or A5 = #2 B5 = 23 2nd st ca C5 = #1 D5 = 1 1st st or =SUMPRODUCT(--(A2:A4=C5),--(B2:B4=D5),--(C2:C4=A5),--(D2:D4=B5),E2:E4) -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Yes, I think you get the idea. The sheets are 200 to 1300 rows long so the result to the 2nd one would be below somewhere. The idea would be to paste the formula into the whole sheet and start filling in the miles and as you go each "second set" would auto populate and you would finish in half the time. "T. Valko" wrote: OK, that helps...but it's not entirely clear! So, based on that sample my best guess for the first result is 485 but I don't see a result for the second one. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
Look up a value from a row after verifying two other values
I had a feeling that would be the case. Oh well!
-- Biff Microsoft Excel MVP "Paul W" wrote in message ... I do not think there is a way to accomplish what I am looking for. This formula below returned a circular reference error. Even the idea behind it though would work only if I were building the spreadsheet from scratch and not just populating the "miles" field. Thank you for your help. "T. Valko" wrote: Assume this data is in the range A2:E4 - Comma delimited columns store,address,2nd store,address,Miles #1,1 1st st or,#2,23 2nd st CA,485 #1,1 1st st or,#3,48 a st ca,385 #1,1 1st st or,#4,65 r st ca,426 To lookup: #2, 23 2nd st ca, #1,1 1st st or A5 = #2 B5 = 23 2nd st ca C5 = #1 D5 = 1 1st st or =SUMPRODUCT(--(A2:A4=C5),--(B2:B4=D5),--(C2:C4=A5),--(D2:D4=B5),E2:E4) -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Yes, I think you get the idea. The sheets are 200 to 1300 rows long so the result to the 2nd one would be below somewhere. The idea would be to paste the formula into the whole sheet and start filling in the miles and as you go each "second set" would auto populate and you would finish in half the time. "T. Valko" wrote: OK, that helps...but it's not entirely clear! So, based on that sample my best guess for the first result is 485 but I don't see a result for the second one. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... I will try, The spreadsheet is at work and i am home now. store address 2nd store address Miles #1 1 1st st or #2 23 2nd st CA 485 #1 1 1st st or #3 48 a st ca 385 #1 1 1st st or #4 65 r st ca 426 #2 23 2nd st ca #1 1 1st st or This is the info I need #2 23 2nd st ca #3 48 a st ca This is the info I need "T. Valko" wrote: Can you post a small sample of the data that demonstrates what you're looking for? From your written description I'm have a hard time visualizing the setup and what you're trying to do. -- Biff Microsoft Excel MVP "Paul W" wrote in message ... Pete, Thanks for the idea. It is a good one. The problem is, I have to have a programmer import the file into an AS400 program once I have figured the time and miles and the 2d won't work for that. Does anyone using maybe VBA have any ideas? "smartin" wrote: Pete_UK wrote: Have you thought about doing the mileage chart as a 2-D table? Four stores A to D would be laid out like this: A B C D A - B - C - D - where the vertical list is the "from" store and the horizontal list is the "to" store. Obviously, the diagonal is meaningless (or all zero). The top triangle is a mirror image of the bottom triangle, although you could just have a single triangle, as it doesn't really matter which is "from" and which is "to". You often get such mileage charts in road atlases. Hope this helps. Pete On Aug 29, 11:49 pm, Paul W <Paul wrote: I have a spreadsheet that I am populating with miles from point to point. Each spreadsheet has the same point tp oint listed twice, the second time in reverse order. I would love to have a formula that looks throught the entire K Column for the point in the D column and the D column for the point in the K column and return the value from the same row in the P column. Basically once I have looked up the miles from a store to another store I don't want to have to find the row below that has the same two store in reverse order and populate the miles field with the same information Is this possible? Pete, Good idea! |
All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com