Challenge :) formula or VBA

D

DavidG007

Board Regular
Joined
Jul 6, 2018
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hey, can you send a quick screenshot of your data please? I think I can see what you want but need to double check!
 
Upvote 0
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($C$1,",",REPT(",",LEN($C$1))),(ROW()-1)*LEN($C$1)+1,LEN($C$1))),",",""),CHAR(34),"")[/TD]
[TD][/TD]
[TD]"400000","400100","400200","400300","400400"[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Drag the formula down and you'll get this:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]400000[/TD]
[TD][/TD]
[TD]"400000","400100","400200","400300","400400"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]400100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]400200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]400300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]400400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Then you can put Revenue into column B manually, if that is what you are after?
 
Last edited:
Upvote 0
You want split Cell 'C1' with 'D2' same my picture, you right?

Code VBA to run it:
Code: 
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:
W3h2mBI.jpg
 
Upvote 0
sadboy309 said:
You want split Cell 'C1' with 'D2' same my picture, you right?

Code VBA to run it:
Code: 
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:
W3h2mBI.jpg
Click to expand...






Thanks for the very speedy response, yes but "revenue" only appears once. Unfortunately I can't attach a screenshot
 
Upvote 0
with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]"400000","400100","400200","400300","400400","400500","400700","400900"[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]400000[/td][td=bgcolor:#E2EFDA]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Revenue[/td][td][/td][td]400100[/td][td]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]400200[/td][td=bgcolor:#E2EFDA]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]400300[/td][td]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]400400[/td][td=bgcolor:#E2EFDA]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]400500[/td][td]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]400700[/td][td=bgcolor:#E2EFDA]Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]400900[/td][td]Revenue[/td][/tr]
[/table]

Code: 
[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]
 
Last edited:
Upvote 0
[TABLE="width: 1632"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]


Here is a better example of my data, hope this helps;

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case Is = "1", "2", "3" 'First character is a balace sheet account[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return BSdefaultValue[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case Is = "(Bypass)"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "None"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case Is = "400000","400100","400200","400300","400400","400500","400700","400900","400901","440000","470000","491000","492000","495000" 'Revenue from the trial balance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "Revenue"[/TD]
[/TR]
[TR]
[TD]Case Is = "431000","431005","432000","432010","433000","433010","434000","434010", "435000","436000","436010","437000"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "Revenue"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case Is = "500203"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "COS_CFR"[/TD]
[/TR]
[TR]
[TD]Case Is = "500201"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "COS_LFR"[/TD]
[/TR]
[TR]
[TD]Case Is = "500202"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "COS_MFR"[/TD]
[/TR]
[TR]
[TD]Case Is = "500204"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "COS_SaaS"[/TD]
[/TR]
[TR]
[TD]Case Is = "500000","500200","500900","501000","520000","521000","531000","532000","533000","533010","534000","534010","535000","536000","537000","539510","658600"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "COS"[/TD]
[/TR]
[TR]
[TD]Case Is = "699999","810100","830500","850000","850500","851000"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Return "ADM"[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
240
Fluff
Fluff
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
548
Peter_SSs
Peter_SSs
S
  • Question Question
VBA Challenge
Replies
2
Views
476
SnowyBry
S
K
  • Question Question
Revenue ramp up and ramp down formula needed
Replies
0
Views
2K
KSU1016
K
M
  • Question Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
915
MichaelMorris
M

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

Share this page

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
Back
Top