Re-split csv value

gleamng

Board Regular
Joined
Oct 8, 2016
Messages
67
Good day everyone,
i have no formal knowledge about vba programming, i hereby seek your help.
The macro below was developed by this forum member Dante Amor, much gratitude to him
Ver. No. is Column A, Names = Col B & Deduction = Col. C

instead of the macro to insert the split results for all cell under deduction into columns H onward, i want the macro the do the spit based on criteria in column A.
what i mean is that once i enter the ver. no. in cell "K1" in Sheet2, while the table is in Sheet1 (image sample1) macro should split the merged deduction into a table from cell "K5" just as displayed in the attached image named (sample2)

Sub deductions()
Dim a As Variant, b() As Variant, c As Variant, ded As Variant
Dim dic As Object, i As Long, col As Long, tax As String

Set dic = CreateObject("Scripting.Dictionary")
a = Range("C2", Range("C" & Rows.Count).End(3)).Value2

For i = 1 To UBound(a, 1)
For Each c In Split(a(i, 1), ",")
If InStr(1, c, "=") > 0 Then
tax = Split(c, "=")(0)
ded = Val(Replace(Split(c, "=")(1), "N", "", , , vbTextCompare))
If Not dic.exists(tax) Then
col = col + 1
ReDim Preserve b(1 To UBound(a, 1) + 1, 1 To col)
dic(tax) = col
b(1, col) = tax
End If
b(i + 1, dic(tax)) = ded
End If
Next
Next
Range("H1").Resize(UBound(a) + 1, col).Value = b
End Sub

Thank you all for your continued support.
 

Attachments

  • sample1.jpg
    sample1.jpg
    246 KB · Views: 3
  • sample2.jpg
    sample2.jpg
    203.4 KB · Views: 3

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,122,988
Messages
5,599,217
Members
414,297
Latest member
dalkarl

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
Top