| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IINDEX and MATCH formula with more than one return value
I am trying to combine information from three different reports from SAP that
I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
I don't have the exact answer to your question, but maybe reading the link
below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
Thank you Niek, I did look at that link before but I don't think I can use it
in my application. Below is a simple version of what I am trying to do. A B F G Order Parts Order Parts 11 11 generator 22 11 shim 33 22 contactor 33 sensor Column F & G have been copied from a report and inserted into my sheet, I am trying to match the parts up to the order number in column A. I have other data from 2 other reports that I am doing the same thing for but have ommitted from this example to simplify. I was using INDEX and MATCH but my problem is that Order 11 has two parts, the generator and shim. Is there someway to get both of these parts to show in column B? Thank you so much for your help! "Niek Otten" wrote: I don't have the exact answer to your question, but maybe reading the link below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
hi awest,
See the attachments to my posts at: http://www.techsupportforum.com/micr...la-issues.html -- Cheers macropod [Microsoft MVP - Word] "awest" wrote in message ... Thank you Niek, I did look at that link before but I don't think I can use it in my application. Below is a simple version of what I am trying to do. A B F G Order Parts Order Parts 11 11 generator 22 11 shim 33 22 contactor 33 sensor Column F & G have been copied from a report and inserted into my sheet, I am trying to match the parts up to the order number in column A. I have other data from 2 other reports that I am doing the same thing for but have ommitted from this example to simplify. I was using INDEX and MATCH but my problem is that Order 11 has two parts, the generator and shim. Is there someway to get both of these parts to show in column B? Thank you so much for your help! "Niek Otten" wrote: I don't have the exact answer to your question, but maybe reading the link below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
Thank you Macropod--these formulas are what I was looking for. I was hoping
for a little more automation with my worksheet but I think this will work great! I appreciate your help very much! "macropod" wrote: hi awest, See the attachments to my posts at: http://www.techsupportforum.com/micr...la-issues.html -- Cheers macropod [Microsoft MVP - Word] "awest" wrote in message ... Thank you Niek, I did look at that link before but I don't think I can use it in my application. Below is a simple version of what I am trying to do. A B F G Order Parts Order Parts 11 11 generator 22 11 shim 33 22 contactor 33 sensor Column F & G have been copied from a report and inserted into my sheet, I am trying to match the parts up to the order number in column A. I have other data from 2 other reports that I am doing the same thing for but have ommitted from this example to simplify. I was using INDEX and MATCH but my problem is that Order 11 has two parts, the generator and shim. Is there someway to get both of these parts to show in column B? Thank you so much for your help! "Niek Otten" wrote: I don't have the exact answer to your question, but maybe reading the link below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
The file with your formulas work great if I have only one input line but I
have a lot of different orders I need to be able to get the information for and I don't want to rewrite the formulas for each order. Is there a way to get around this? "macropod" wrote: hi awest, See the attachments to my posts at: http://www.techsupportforum.com/micr...la-issues.html -- Cheers macropod [Microsoft MVP - Word] "awest" wrote in message ... Thank you Niek, I did look at that link before but I don't think I can use it in my application. Below is a simple version of what I am trying to do. A B F G Order Parts Order Parts 11 11 generator 22 11 shim 33 22 contactor 33 sensor Column F & G have been copied from a report and inserted into my sheet, I am trying to match the parts up to the order number in column A. I have other data from 2 other reports that I am doing the same thing for but have ommitted from this example to simplify. I was using INDEX and MATCH but my problem is that Order 11 has two parts, the generator and shim. Is there someway to get both of these parts to show in column B? Thank you so much for your help! "Niek Otten" wrote: I don't have the exact answer to your question, but maybe reading the link below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IINDEX and MATCH formula with more than one return value
Hi awest,
It'd take considerably more work to accommodate mutiple orders in the one table - if, indeed, doing so is possible. Alternatives you might explore include setting up multiple query tables, or using a macro to sequentially input the individual order #s into the query table then copy out & store the results elsewhere. -- Cheers macropod [Microsoft MVP - Word] "awest" wrote in message ... The file with your formulas work great if I have only one input line but I have a lot of different orders I need to be able to get the information for and I don't want to rewrite the formulas for each order. Is there a way to get around this? "macropod" wrote: hi awest, See the attachments to my posts at: http://www.techsupportforum.com/micr...la-issues.html -- Cheers macropod [Microsoft MVP - Word] "awest" wrote in message ... Thank you Niek, I did look at that link before but I don't think I can use it in my application. Below is a simple version of what I am trying to do. A B F G Order Parts Order Parts 11 11 generator 22 11 shim 33 22 contactor 33 sensor Column F & G have been copied from a report and inserted into my sheet, I am trying to match the parts up to the order number in column A. I have other data from 2 other reports that I am doing the same thing for but have ommitted from this example to simplify. I was using INDEX and MATCH but my problem is that Order 11 has two parts, the generator and shim. Is there someway to get both of these parts to show in column B? Thank you so much for your help! "Niek Otten" wrote: I don't have the exact answer to your question, but maybe reading the link below helps you on your way http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "awest" wrote in message ... I am trying to combine information from three different reports from SAP that I sent to excel files. I am using the INDEX and MATCH formulas to match order numbers from each of these reports to get description, time worked, employee name, part consumed and qty of part consumed for each order. My problem is that multiple parts can be used for one order but when I do the INDEX and MATCH formula I can only get a return for one of the three parts, for example. Before I move on with combining these reports I wanted to see if there is capabilty for excel to "insert a cell" so that multiple returns of the formula can be matched to one order. Hope this makes sense. I appreciate any help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match then return a value | Excel Discussion (Misc queries) | |||
| Lookup formula to return all instances of match? | Excel Discussion (Misc queries) | |||
| need to return next match of table, only finding first match...HEL | Excel Worksheet Functions | |||
| MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
| Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
Linear Mode
