separating data

[jswaim]

Hi,
I have some data that I am trying to separate into individual cells. I have four columns of data. A,B,C, and D. Each row pertains to an individual model (product). So in Column A, I have model codes listed, Column B, is where the options that are unique to the particular model. Column B is where I am having trouble.
A B C D
Model Options Color Trim
CC10906 AG1, B30, FE9, ETC. 59U 193
CC10906 AE7, AJ1, 41U 19D

I need to separate the option codes in column B into individual cells going down the page. I would like the options to carry down the page until they are exhausted and then populate column A with the next Model Code and start the process all over again. I hope I am making sense here.
Thanks for any advice,
Jason

 

[jindon]

Hi
try

Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, e
a = Range("a1").CurrentRegion.Resize(,4).Value
ReDim b(1 To Rows.Count, 1 To 4)
For i = 1 To UBound(a,1)
     If InStr(a(i,2),",")>0 Then
          For Each e In Split(a(i,2),",")
               n = n + 1
               For iii = 1 To UBound(a,2) : b(n,iii) = IIf(iii<>2,a(i,iii),e) : Next
          Next
     Else
          n = n + 1
          For iii = 1 To UBound(a,2) : b(n,iii) = a(i,iii) : Next
     End IF
Next
Range("f1").Resize(n,4).Value = b
End Sub

 

[jswaim]

Thank you for the response. However, this did not work for me. It just duplicated the data leaving all the options except one in column B. What I am trying to accomplish is to find out my product mix. I am trying to figure out, for example how many CC10936's I have with the package group 1LT and the option G80. Each model code in column A is a single product with it's own options (options are in Column B,C, and D) (Column B are the actual options, Column C are paint codes, and D is Trim Codes)
If you notice the last 3 digit code in Column B is like 1LT, 2LT, 3LT, 1LZ, etc. These are the package groups.
I need each CC10936 separated into it's respectable package group, then I need it to tell me what percentage of my inventory has certain options and paint/trim codes for example: How many CC10936's with the 1LT package I have option code G80. Or how many CC10936's with the 1LT package have the 59U paint code located in column C.
Again, the paint codes are in Column C and the Trim Codes are in Column D.
The end product should look something like.
CC10936(from column A)
1LT=5 (from column B)
G80=3 (from column b)
AG1=2 (from column B)
50U=1 (from column C)
59U=2 (from column C)
69D=1 (from column D)
This is just an example. The macro should cycle through all the options until they are exhausted.
Thanks for your help, hope this makes more sense.

 

[jindon]

Sorry, I don't understand what you are trying to do.

Post sample with before/after.

 

[Norie]

Jason

Why have you posted this multiple times?

 

[jswaim]

I posted it a few times because there are a few different angles to tackle this problem and it is a bit difficult to explain, especially without being able to show my spread sheets. My companies firewall will not allow me to download Colo's tool. Sorry for any confusion.

 

[jswaim]

Thanks for staying with me Jindon.

If I could show my spreadsheet I would, but I cannot download the tool. I will do my best to show before and after. I will label each cell with the data beside it (A1) (B1) etc.

(A1) is the model code (B1) are the option codes (C1) are the Color Codes and (D1) are the Trim Codes. I will start with the raw data first and then show how it should look, as the final product.

(A1) MODEL (B1) OPTIONS (C1) COLOR (D1) TRIM
(A2) CC10703 (B2) AE7, B30, G80, R34, T43, 1LT (C2) 59U (D2) 69D
(A3) CC10703 (B3) A95, B30, G24, S24, 2LT (C3) 25U (D3) 193
(A4) CC10936 (B4) AN3, AL4, N24, 3LT (C4) 41U (D4) 313

I have about a hundred model codes and thousand of option and color/trim combinations. If you notice in Column B the last option code is always like a 1LT, 2LT, 3LT, 1LZ, 1LS, OR 1WT. These are called package groups. Basically, what I am trying to do is to find out for example how many CC10703 models I have with the 1LT package. Of the CC10703's with the 1LT package how many have the option code G80, or how many have the color code (C1) 59U.
With the above data in mind the final product should look something like:
(A2)CC10703 1LT=1 (B2) CC10703 2LT=1 (C2) CC10936 3LT=1
(A3)AE7=1 (B3)A95=1 (C3) AN3=1
(A4)G80=1 (B4)B30=1 (C4) AL4=1
(A5)R34=1 (B5)G24=1 (C5) N24=1
(A6)T43=1 (B6)S24=1 (C6) 41U=1
(A7)59U=1 (B7)25U=1 (C7) 313=1
(A8)69D=1 (B8)193=1
Notice how each model has the number of package groups next to it. See how there is a CC10703 with the 1LT package(A2) and a CC10703 with the 2LT package(B2)? Please let me know if this makes any more sense.
Thanks,

 

[Joe4]

Duplicate. See re-stated question here:
http://www.mrexcel.com/board2/viewtopic.php?t=288254&highlight=