Generating a string from other cells/CSV file using a for loop

Hello,

I've been assigned a project that is fairly simple. Basically, I have a sheet with lots of data for different aftermarket products and I need to combine data from different cells to create one long string, or "title". I have written a pseudocode for what I want to be done, but I am very new to excel programming.

I have two different files: one excel workbook file and a CSV file. The workbook file contains lots of data, but I only need to use data from two columns. One of the columns has a short, default title of basically what the product is, and the other column has different brands and part numbers that the product replaces, with each brand/part number separated by an HTML break tag (for example, one of these cells may look like this: "Cub Cadet #######<br>John Deere #######<br>Toro ######" - note that the part numbers may contain letters and numbers). The CSV file contains a column of different brand names.

The task is simple: I want to write a code that searches looks for each brand from the CSV file in the cell containing brand and part number information, and if found, take the brand name and part number and append it to the cell with the default title - all while the total number of characters is less than or equal to 128. So if the default title cell is "Lawn Mower Blade", I want the end result to be something like "Lawn Mower Blade Replaces John Deere ####### Cub Cadet ####### Toro ######".

Here is the pseudocode for what I'd like to happen (or of course if there is a better/easier way of doing it, I'd much appreciate it!)


For i = 1 To 36 (36 being the total number of brand names on the list) {

Find "Brand i" in the 'Replacement Brand/Part Number' cell

Use extraction formula (shown below), taking from starting position of "brand i" and ending at starting position of next "<br>"

IF: length of output of extraction formula + length of everything before < 129, add to title cell

ELSE: break loop }

*Extraction formula: =" "&MID(O2,FIND("Brand i",O2),FIND("<br>",O2,FIND("Brand i",O2))-FIND("Brand i",O2)) -- This is the formula I use to extract the brand and part number following it; column O contains the cells with brand/part replacement info.


The loop is applied to each row/product. I would really appreciate any help given. Again, I'm pretty new to excel programming any new or better code than mine would be much appreciated too - thank you in advance!

I've attached a sample workbook. I didn't attach the CSV, but it just has a list of brand names listed by priority (for example, John Deere is at the top because the first brand/number we want is John Deere).

Here's a sample of the CSV file

Please Login or Register  to view this content.

The first code has the title formatted correctly, but I need the brands listed in the order from the CSV file - I also need only the brands from the CSV, meaning if there is a brand in the replacement info cell that isn't on the CSV, it shouldn't be put in the title. It also doesn't look like it takes the 128 characters max into account. And I don't have Power Query to try the other solution.

Please Login or Register  to view this content.

Originally Posted by jindon

Please Login or Register  to view this content.

This is very close to how I need it. However, when I use this code, the title cell looks like this:


Replacement Mower Belt Replaces Cub Cadet 132013-C1 464351-R1 Craftsman 501089 Ariens 07211700 07211900 Ariens 07211700 Ariens 07211900 Craftsman 501089 Cub Cadet 132013-C1 Cub Cadet 464351-R1


The first half is perfect, but the titles always end with redundant brands and numbers - always after a double space. I need to get rid of this information. It should look like this (with the red underlined text deleted):

Replacement Mower Belt Replaces Cub Cadet 132013-C1 464351-R1 Craftsman 501089 Ariens 07211700 07211900 Ariens 07211700 Ariens 07211900 Craftsman 501089 Cub Cadet 132013-C1 Cub Cadet 464351-R1

Also does this code take the 128 characters into account?

1) Don't quote full post unless you really need it to, forum doesn't like it.

2) change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

3) What do you want to do when it exceeds 128 characters?

Thanks so much! When the title exceeds 128 characters, I want to 'undo' the last brand/number that was put in.

See if this works as you expected.

Please Login or Register  to view this content.

Thanks you so much! This does just what I need. Thanks for all your help!