trishcollins
Board Regular
- Joined
- Jan 7, 2006
- Messages
- 71
I have a table that has a list of requirements from various documents. The table is called "Combined_Req". Each requirement is assigned a unique identifier "ID", that is in the form of "CR-1", "CR-2", etc."
I have another table called "Req_by_Category" where I have consolidated like requirements from the first table and given each one a unique identifier "ENC Req ID", that is in the form of "ENC-1", "ENC-2", "ENC-3", etc. There is a column in this table called "Original Requirement", where I have manually identified all the "CR-x" requirements from the "Combined_Req" table, so I can understand which requirements I have covered off with that new, single requirement. It takes on the form of concatenated CR requirements, so it may have one, two, or multiple identifiers in a single cell. For instance "CR-12, CR-14, CR110", or "CR-3", etc. Note that in a single instance, there is no comma, and in a multiple instance, the last value has no trailing comma.
ENC Req ID, Original Requirement
"ENC-1", "CR-24, CR- 82, CR- 83, CR- 85, CR- 86, CR- 191, CR- 197"
"ENC-4", "CR-13"
In the first table, "Combined_Req", I have a new column called "In ENC Requirements", and I want to do a lookup on the "Req_by_Category" table on the "Original Requirement" column to make sure that I have covered off all the requirements in the new "Req_by_Category" table.
I have tried this:
= VLOOKUP("*"&[@ID]&"*",Req_by_Category[Original Requirement],1,FALSE), and it only returns the result if the ID number being looked up in the in the first position. For example, if I look up "CR-101", then it returns the contents of the concatenated cell "CR-101, CR- 102", which is correct. However, in the very next row, the same formula on "CR-102" results in #N/A, when in fact, it should show the same result as the previous row "CR-101, CR-102". I have checked similar items, and have the same result. In addition, it also picks up any instance where the first item STARTS with the same number, ie. a lookup on "CR-2" brings back "CR-24", NOT "CR-2", as it's finding the first instance that matches the wild cards. So, there has to be an issue with my formula, and I know the wild cards are part of it. I am familiar with VBA, so if someone has code, that would be great.
It's not a huge deal to do manually, but if I decide that a different consolidated requirement is more suitable to the original, or as I add more requirements to the original requirement column (I am not quite done yet), I will have to manually make those changes. This way, if I change the contents of any of any cells in the Original Requirements column, it's automatically reflected in the combined requirements table.
I am just trying to initially show if a requirement has been covered off, and once I get the formula working, I want to instead, show the "ENC Req ID" in the "In ENC Requirements" column, so I can quickly look to see if and what requirement the original requirement is covered under. Ideally, a child-parent relationship would be great, but I don't expect miracles. I don't want to spend more time on this, then actually doing the work
Any suggestions?
Trish
I have another table called "Req_by_Category" where I have consolidated like requirements from the first table and given each one a unique identifier "ENC Req ID", that is in the form of "ENC-1", "ENC-2", "ENC-3", etc. There is a column in this table called "Original Requirement", where I have manually identified all the "CR-x" requirements from the "Combined_Req" table, so I can understand which requirements I have covered off with that new, single requirement. It takes on the form of concatenated CR requirements, so it may have one, two, or multiple identifiers in a single cell. For instance "CR-12, CR-14, CR110", or "CR-3", etc. Note that in a single instance, there is no comma, and in a multiple instance, the last value has no trailing comma.
ENC Req ID, Original Requirement
"ENC-1", "CR-24, CR- 82, CR- 83, CR- 85, CR- 86, CR- 191, CR- 197"
"ENC-4", "CR-13"
In the first table, "Combined_Req", I have a new column called "In ENC Requirements", and I want to do a lookup on the "Req_by_Category" table on the "Original Requirement" column to make sure that I have covered off all the requirements in the new "Req_by_Category" table.
I have tried this:
= VLOOKUP("*"&[@ID]&"*",Req_by_Category[Original Requirement],1,FALSE), and it only returns the result if the ID number being looked up in the in the first position. For example, if I look up "CR-101", then it returns the contents of the concatenated cell "CR-101, CR- 102", which is correct. However, in the very next row, the same formula on "CR-102" results in #N/A, when in fact, it should show the same result as the previous row "CR-101, CR-102". I have checked similar items, and have the same result. In addition, it also picks up any instance where the first item STARTS with the same number, ie. a lookup on "CR-2" brings back "CR-24", NOT "CR-2", as it's finding the first instance that matches the wild cards. So, there has to be an issue with my formula, and I know the wild cards are part of it. I am familiar with VBA, so if someone has code, that would be great.
It's not a huge deal to do manually, but if I decide that a different consolidated requirement is more suitable to the original, or as I add more requirements to the original requirement column (I am not quite done yet), I will have to manually make those changes. This way, if I change the contents of any of any cells in the Original Requirements column, it's automatically reflected in the combined requirements table.
I am just trying to initially show if a requirement has been covered off, and once I get the formula working, I want to instead, show the "ENC Req ID" in the "In ENC Requirements" column, so I can quickly look to see if and what requirement the original requirement is covered under. Ideally, a child-parent relationship would be great, but I don't expect miracles. I don't want to spend more time on this, then actually doing the work
Any suggestions?
Trish
Last edited:
