StuffofLegend
New Member
- Joined
- Oct 22, 2015
- Messages
- 8
Ok, so I have a problem with extracting some data from an Excel table. Forgive me if I don’t explain my problem well (my Excel knowledge is limited). The layout of my spreadsheet is as follows: Individual claim items make up the rows in my main data sheet (my ‘Claim Detail’ sheet), and the specific columns that I am interested in are “Dispatch ID” (the job number, which is column A), “Item Number” (column B), “Claim Label” (which is the month of the claim, and is column F), and “External ID” (column M).
What I want to do is enter a formula on a separate sheet (‘Minor Projects’), which searches on the Claim Detail sheet for a specific type of External ID (the value must start with either an “RRT” or an “M”), and once Excel has found a cell containing either of these values, it needs to check the values of one other column within the row that the value is located: It needs to check column F and only return a value if column F is displaying the current claim month (October 2015 in this case, however I can use an absolute cell reference $A$1 to let the formula know what the value of the current claim month should be. The value in A1 will change to November 2015 next month).
Once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it returns the External ID in the cell which contains the formula. A potential problem is that in any given month there will be up to 50 claim items which could have an RRT or M value, and as an example there could be 5 claim lines which all have the same M value, e.g. M1677. It is the Dispatch ID and Item Number columns which provide the extra info to show that the 5 claim lines are unique e.g. 2 of the 5 claim lines will have a Dispatch ID of 7112 and 2 will have a Dispatch ID of 7113. That still leaves two claim lines which have the same External ID, Claim Label and Dispatch ID, and it is the Item Number column which provides the unique entry.
So to summarise, once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it needs to return the External ID in cell A25 of my separate sheet. Then in cell B25 of my separate sheet it needs to return the corresponding Dispatch ID and in cell C25 of my separate sheet it needs to return the corresponding Item Number. I realise I would need to copy the formulas down for approx. 50 rows because of the number of claim items each month which could have an RRT or M value. I need to ensure that none of the lines in my separate sheet are duplicates. My guess would be that I need to use a combination of the INDEX, VLOOKUP and IF functions. If anyone can help, that would be very much appreciated.
What I want to do is enter a formula on a separate sheet (‘Minor Projects’), which searches on the Claim Detail sheet for a specific type of External ID (the value must start with either an “RRT” or an “M”), and once Excel has found a cell containing either of these values, it needs to check the values of one other column within the row that the value is located: It needs to check column F and only return a value if column F is displaying the current claim month (October 2015 in this case, however I can use an absolute cell reference $A$1 to let the formula know what the value of the current claim month should be. The value in A1 will change to November 2015 next month).
Once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it returns the External ID in the cell which contains the formula. A potential problem is that in any given month there will be up to 50 claim items which could have an RRT or M value, and as an example there could be 5 claim lines which all have the same M value, e.g. M1677. It is the Dispatch ID and Item Number columns which provide the extra info to show that the 5 claim lines are unique e.g. 2 of the 5 claim lines will have a Dispatch ID of 7112 and 2 will have a Dispatch ID of 7113. That still leaves two claim lines which have the same External ID, Claim Label and Dispatch ID, and it is the Item Number column which provides the unique entry.
So to summarise, once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it needs to return the External ID in cell A25 of my separate sheet. Then in cell B25 of my separate sheet it needs to return the corresponding Dispatch ID and in cell C25 of my separate sheet it needs to return the corresponding Item Number. I realise I would need to copy the formulas down for approx. 50 rows because of the number of claim items each month which could have an RRT or M value. I need to ensure that none of the lines in my separate sheet are duplicates. My guess would be that I need to use a combination of the INDEX, VLOOKUP and IF functions. If anyone can help, that would be very much appreciated.
