Hello,
I need some help creating a vba code for the following situation.
I have a table with 15 headers (different business lines) from column A-O. In column P, I have multiple business codes (numbers) separated by the delimiter "|".
What I have done is used text to column to separate these numbers into its own columns on the right of column P.
Then I created a formula for each line of business from columns A-O. Where A$3 is updated to B$3, C$3 and so forth up to column P.
=IF(ISERROR(FIND(XLOOKUP(VALUE(REPLACE($Q4,1,FIND("- ",$Q4),"")),VALUE(dsmt!$V$2:$V$16430),dsmt!$AK$2:$AK$16430),A$3)),"","X")
I am trying to create a vba code that will loop through all number codes after column P. There is no set of amount of number codes from column P. Some have 1 number code, 5 number codes, or could possibly have 242 number codes. Using the above formula as a base, where $Q4 can change to R4 and so forth. Also, for each instance it finds from A$3, mark as an X one time.
so let's say the line of business in cell A3 is Tech1, and the number code counts are 5 and 2 of those numbers match the line of business, then mark as an X one time.
I'm looking to do a search using vba (loop/append) to search for each code in column Q-end of row data), and mark line of business name with an X when it finds a match (even if match is there multiple times).
Below is a small dataset you can use. as well as a screenshot.
The 'dsmt' sheet it is comparing it to is just a list of number, feel free to add these numbers as well as random numbers.
I appreciate any kind of help.
Thank you,
I need some help creating a vba code for the following situation.
I have a table with 15 headers (different business lines) from column A-O. In column P, I have multiple business codes (numbers) separated by the delimiter "|".
What I have done is used text to column to separate these numbers into its own columns on the right of column P.
Then I created a formula for each line of business from columns A-O. Where A$3 is updated to B$3, C$3 and so forth up to column P.
=IF(ISERROR(FIND(XLOOKUP(VALUE(REPLACE($Q4,1,FIND("- ",$Q4),"")),VALUE(dsmt!$V$2:$V$16430),dsmt!$AK$2:$AK$16430),A$3)),"","X")
I am trying to create a vba code that will loop through all number codes after column P. There is no set of amount of number codes from column P. Some have 1 number code, 5 number codes, or could possibly have 242 number codes. Using the above formula as a base, where $Q4 can change to R4 and so forth. Also, for each instance it finds from A$3, mark as an X one time.
so let's say the line of business in cell A3 is Tech1, and the number code counts are 5 and 2 of those numbers match the line of business, then mark as an X one time.
I'm looking to do a search using vba (loop/append) to search for each code in column Q-end of row data), and mark line of business name with an X when it finds a match (even if match is there multiple times).
Below is a small dataset you can use. as well as a screenshot.
The 'dsmt' sheet it is comparing it to is just a list of number, feel free to add these numbers as well as random numbers.
| Column V | ||||||||||||||||||||
| Group1 | Group2 | Group3 | Group4 | |||||||||||||||||
| Tech 1 | Tech 2 | Tech 3 | Tech 4 | Tech 5 | Tech 6 | Tech 7 | Tech 8 | Tech 9 | Tech 10 | Tech 11 | Tech 12 | Tech 13 | Tech 14 | Non-Tech | Managed Segment (Home)- NodeNumber | |||||
| Capital Planning [L6](9920988) - 9920988|CCA Controllers [L8](5017) - 5017|Public Side Credit Trading [L8](20205) - 20205|Finance and Risk Shared Services [L6](9908260) - 9908260|Investments Pensions and Convergence Risk [L7](4978) - 4978 | Capital Planning [L6](9920988) - 9920988 | CCA Controllers [L8](5017) - 5017 | Public Side Credit Trading [L8](20205) - 20205 | Finance and Risk Shared Services [L6](9908260) - 9908260 | Investments Pensions and Convergence Risk [L7](4978) - 4978 | |||||||||||||||
| Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury Allocations [L9](24712) - 24712|Treasury Allocations [L9](24712) - 24712 | Treasury [L6](9905121) - 9905121 | Treasury - Other ICG- Core [L6](16728) - 16728 | Treasury Allocations [L9](24712) - 24712 | Treasury Allocations [L9](24712) - 24712 | ||||||||||||||||
| Treasury [L6](9905121) - 9905121|PU/CO - Treasury [L6](9821741) - 9821741|PU/CO - Treasury [L6](9821741) - 9821741|PU/CO - Treasury [L7](9821020) - 9821020|PU/CO - Treasury [L7](9821020) - 9821020 | Treasury [L6](9905121) - 9905121 | PU/CO - Treasury [L6](9821741) - 9821741 | PU/CO - Treasury [L6](9821741) - 9821741 | PU/CO - Treasury [L7](9821020) - 9821020 | PU/CO - Treasury [L7](9821020) - 9821020 | |||||||||||||||
| Controller [L6](5002) - 5002 | Controller [L6](5002) - 5002 |
I appreciate any kind of help.
Thank you,
