Challenge :) formula or VBA

[DavidG007]

I have an issue that I don't know how to easily resolve. I need to convert the following line of text.

Column 'C1' would typically be this (but will be multiple lengths);
Case Is = "400000","400100","400200","400300","400400","400500","400700","400900"

Column 'D2' would say 'Revenue'

What I need to be able to do is break down Column 'C1' and include the text in Column 'D2'

My final answer would look like this;

Column A Column B
400000 Revenue
400100 Revenue
400200 Revenue


etc etc.

The formula or code would then need to do exactly the same check on subsequent rows as the pattern

I really hope this makes sense and there are a few of you that fancy the challenge

massive thanks for any replies

 

[tyija1995]

Hey, can you send a quick screenshot of your data please? I think I can see what you want but need to double check!

 

[tyija1995]

	A	B	C	D
1	=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$1,",",REPT(",",LEN($C$1))),(ROW()-1)*LEN($C$1)+1,LEN($C$1))),",",""),CHAR(34),"")		"400000","400100","400200","400300","400400"

<tbody>
</tbody>

Drag the formula down and you'll get this:

	A	B	C	D
1	400000		"400000","400100","400200","400300","400400"
2	400100
3	400200
4	400300
5	400400

<tbody>
</tbody>

Then you can put Revenue into column B manually, if that is what you are after?

 

[sadboy309]

You want split Cell 'C1' with 'D2' same my picture, you right?

Code VBA to run it:

Sub splitText(ColA As String, ColB As String)
Dim k As Integer
Dim ch1() As String, ch2() As String


ch1 = Split(ColA, ",")
ch2 = Split(ColB, ",")
k = 1
For i = 0 To UBound(ch1)
Cells(k, 1) = Replace(ch1(i), """", "")
Cells(k, 2) = Replace(ch2(i), """", "")
k = k + 1
Next


End Sub

'Code Main Test
Sub TestsplitText()
Call splitText(Range("C1"), Range("D2"))
End Sub

Picture example:

 

[DavidG007]

You want split Cell 'C1' with 'D2' same my picture, you right?

Code VBA to run it:

Sub splitText(ColA As String, ColB As String)
Dim k As Integer
Dim ch1() As String, ch2() As String


ch1 = Split(ColA, ",")
ch2 = Split(ColB, ",")
k = 1
For i = 0 To UBound(ch1)
Cells(k, 1) = Replace(ch1(i), """", "")
Cells(k, 2) = Replace(ch2(i), """", "")
k = k + 1
Next


End Sub

'Code Main Test
Sub TestsplitText()
Call splitText(Range("C1"), Range("D2"))
End Sub

Picture example:

Thanks for the very speedy response, yes but "revenue" only appears once. Unfortunately I can't attach a screenshot

 

[sandy666]

with PowerQuery aka Get&Transform:

Column1	Column2		Column1	Column2
"400000","400100","400200","400300","400400","400500","400700","400900"			400000	Revenue
	Revenue		400100	Revenue
			400200	Revenue
			400300	Revenue
			400400	Revenue
			400500	Revenue
			400700	Revenue
			400900	Revenue

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Fill = Table.FillUp(Split,{"Column2"}),
    Filter = Table.SelectRows(Fill, each ([Column1] <> null))
in
    Filter[/SIZE]

 

[DavidG007]

Here is a better example of my data, hope this helps;
Case Is = "1", "2", "3" 'First character is a balace sheet account
	Return BSdefaultValue
Case Is = "(Bypass)"
	Return "None"
Case Is = "400000","400100","400200","400300","400400","400500","400700","400900","400901","440000","470000","491000","492000","495000" 'Revenue from the trial balance
	Return "Revenue"
Case Is = "431000","431005","432000","432010","433000","433010","434000","434010", "435000","436000","436010","437000"
	Return "Revenue"
Case Is = "500203"
	Return "COS_CFR"
Case Is = "500201"
	Return "COS_LFR"
Case Is = "500202"
	Return "COS_MFR"
Case Is = "500204"
	Return "COS_SaaS"
Case Is = "500000","500200","500900","501000","520000","521000","531000","532000","533000","533010","534000","534010","535000","536000","537000","539510","658600"
	Return "COS"
Case Is = "699999","810100","830500","850000","850500","851000"
	Return "ADM"

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[DavidG007]

Hi Sandy, the powerquery is a great shout, I tried to use your code but unfortunately it doesn't allow a straight paste, do I need to put anything before your code?

Huge thank you

 

[sandy666]

I'd like to see example in a table form because from the post#7 I really don't know "what-is-what".
I suggest create representative example of source data (and expected result), upload excel file into any service type GoogleDrive, OneDrive or similar then post link here to the shared file

 

[DavidG007]

Hi Sandy,

Please see link

https://drive.google.com/open?id=1NzH5GyGTlyQxCRMZCqrltKFlMXGUPhra

Thanks once again