Need your insight and support on the following.
I am creating a product catalog and am encountering some challenges along the way (VBA wise). One of the challenges is product color.
I have a database of items (flat database of some 1000 items/lines X 20 columns) and in each line, amongst other pieces of data, there are colors
Column B contains Color 1 (Column C will have the available stock)
Column D can contain Color 2 (Column E will have the available stock)
Column F can contain Color 3 (Column G will have the available stock)
Column H can contain Color 4 (Column I will have the available stock)
Column J can contain Color 5 (Column K will have the available stock)
So cell by cell (in a row) – this is what you see (for example): Black 50 White 40 Green 75 Yellow 30 Brown 48
The thing is, sometimes there is only one color and sometimes – there are up to 5 (the colors change all the time).
What I want to do is concatenate (in column L) all the COLORS of each record, with a separator : Color 1 | Color 2 | Color 3 | Color 4 | Color 5. (i.e. Black | White | Green | Yellow | Brown)
Obviously, if there is only one color or two, I don't want the "|" appearing if there are no more colors…
So, basically, I am looking for a "dynamic concatenate" code.
I know how to count the number of colors that are listed per line (I simply count the non-empty cells between column B and K, and divide by 2…).
Now I need to create this dynamic concatenate (with the separators) for the colors in each specific line….
I can't think of reasonable and fast way to do it, but I am pretty sure that this is something that can be achieved with an Array.
Any help will be highly appreciated