VBA Code - Loop/Append data in excel

MHamid

Active Member
Joined
Jan 31, 2013
Messages
437
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
1647278803312.png


Column V
Group1Group2Group3Group4
Tech 1Tech 2Tech 3Tech 4Tech 5Tech 6Tech 7Tech 8Tech 9Tech 10Tech 11Tech 12Tech 13Tech 14Non-TechManaged 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) - 4978Capital Planning [L6](9920988) - 9920988CCA Controllers [L8](5017) - 5017Public Side Credit Trading [L8](20205) - 20205Finance and Risk Shared Services [L6](9908260) - 9908260Investments 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) - 24712Treasury [L6](9905121) - 9905121Treasury - Other ICG- Core [L6](16728) - 16728Treasury Allocations [L9](24712) - 24712Treasury 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) - 9821020Treasury [L6](9905121) - 9905121PU/CO - Treasury [L6](9821741) - 9821741PU/CO - Treasury [L6](9821741) - 9821741PU/CO - Treasury [L7](9821020) - 9821020PU/CO - Treasury [L7](9821020) - 9821020
Controller [L6](5002) - 5002Controller [L6](5002) - 5002


I appreciate any kind of help.

Thank you,
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,175,515
Messages
5,897,885
Members
434,686
Latest member
asdf12l3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top