Create .TXT Subsets based on existing Excel data using VBA Macro

Rakesh99932

New Member
Joined
Feb 25, 2020
Messages
23
Office Version
2019, 2016, 2013
Platform
Windows
Hello Guys,
I need to create Subsets for the data which I have, which is dynamic, using Excel VBA.
I have data in the form of table, whose column headings are Code, % & Transaction ID as the below table.

Code
%
Transaction ID
1001​
42.10%​
455152​
1001​
42.10%​
455153​
1001​
42.10%​
455154​
1001​
55.10%​
455155​
1001​
55.10%​
455156​
1001​
55.10%​
455157​
1002​
66.40%​
455158​
1002​
66.40%​
455159​
1002​
66.40%​
455160​
1002​
66.40%​
455161​
1002​
85.70%​
455162​
1003​
85.70%​
455163​
1003​
85.70%​
455164​
1003​
85.70%​
455165​
1003​
85.70%​
455166​
1004​
95.60%​
455167​
1004​
95.60%​
455168​
1004​
95.60%​
455169​
1004​
95.60%​
455170​
1004​
95.60%​
455171​


I need to create subsets in .txt file which only shows Transaction ID's in it based on the combinations between Code and %.

For example, if I filter Code 1001, I can see 42.10% & 55.10%. For the first combination of code 1001 & 42.10%, the transaction ID’s only should be copied to a .txt file and this .txt file should be saved with the naming convection using “TXT_Code%_ddmm” format.

Example of the combinations:

Example 1:

Code
%
Transaction ID
1001​
42.10%​
455152​
1001​
42.10%​
455153​
1001​
42.10%​
455154​

In the above table, i have filtered 1001 Code and 42.10%. I need transaction Id’s only should be copied to a new .txt file, and its naming convection should be as “TXT_1001421_ddmm” format and save this .Txt file in the folder.

Example 2:
Code
%
Transaction ID
1001​
55.10%​
455155​
1001​
55.10%​
455156​
1001​
55.10%​
455157​

In the above table, i have filtered 1001 Code and 55.10%. I need transaction Id’s only should be copied to a new .txt file, and its naming convection should be as “TXT_1001551_ddmm” format and save this file in the folder.

The data is always dynamic and the combinations are also dynamic.

Request your kind help in this matter, as this will save lo of time for the huge data I have.

Thank you for your help in Advance.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
257
check this out.
Assuming the Code % and Transaction ID are in Column A, B, and C respectively
VBA Code:
Sub SubsetTest()
Dim s$, c&, j%, id$, dd%, d() As String, sel As Boolean, folder$, fol As FileDialog, z%, b$, i&, p&
Dim h() As String, t&, per$
s = InputBox("Please, enter the code")
If s <> "" Then c = s Else Exit Sub
Set fol = Application.FileDialog(msoFileDialogFolderPicker)
fol.AllowMultiSelect = False
sel = fol.Show
If sel Then folder = fol.SelectedItems(1) & "\" Else Exit Sub
j = ActiveSheet.Cells(1048576, 1).End(xlUp).Row
For z = 1 To j
If Cells(z, 1) = c Then
If InStr(1, b, Cells(z, 2)) > 0 Then
Else
If b = "" Then b = Cells(z, 2) Else b = b & "|" & Cells(z, 2)
End If
End If
Next z
d = Split(b, "|", , vbTextCompare)
For i = 0 To UBound(d)
For p = 1 To j
If Cells(p, 1) = c And Cells(p, 2) = d(i) Then
If id = "" Then id = Cells(p, 3) Else id = id & vbNewLine & Cells(p, 3)
h = Split(CDec(Cells(p, 2)) * 100, ".", , vbTextCompare)
End If
Next p
For t = 0 To UBound(h)
If IsNumeric(h(t)) Then
If per = "" Then per = h(t) Else per = per & h(t)
End If
Next
Open folder & "TXT_" & c & per & ".txt" For Output As #1
Print #1, id;
Close #1
id = "": per = ""
Next i
End Sub
 

Rakesh99932

New Member
Joined
Feb 25, 2020
Messages
23
Office Version
2019, 2016, 2013
Platform
Windows
check this out.
Assuming the Code % and Transaction ID are in Column A, B, and C respectively
VBA Code:
Sub SubsetTest()
Dim s$, c&, j%, id$, dd%, d() As String, sel As Boolean, folder$, fol As FileDialog, z%, b$, i&, p&
Dim h() As String, t&, per$
s = InputBox("Please, enter the code")
If s <> "" Then c = s Else Exit Sub
Set fol = Application.FileDialog(msoFileDialogFolderPicker)
fol.AllowMultiSelect = False
sel = fol.Show
If sel Then folder = fol.SelectedItems(1) & "\" Else Exit Sub
j = ActiveSheet.Cells(1048576, 1).End(xlUp).Row
For z = 1 To j
If Cells(z, 1) = c Then
If InStr(1, b, Cells(z, 2)) > 0 Then
Else
If b = "" Then b = Cells(z, 2) Else b = b & "|" & Cells(z, 2)
End If
End If
Next z
d = Split(b, "|", , vbTextCompare)
For i = 0 To UBound(d)
For p = 1 To j
If Cells(p, 1) = c And Cells(p, 2) = d(i) Then
If id = "" Then id = Cells(p, 3) Else id = id & vbNewLine & Cells(p, 3)
h = Split(CDec(Cells(p, 2)) * 100, ".", , vbTextCompare)
End If
Next p
For t = 0 To UBound(h)
If IsNumeric(h(t)) Then
If per = "" Then per = h(t) Else per = per & h(t)
End If
Next
Open folder & "TXT_" & c & per & ".txt" For Output As #1
Print #1, id;
Close #1
id = "": per = ""
Next i
End Sub

Hi yinkajewole,
Tnx a lot for your help. This is working good. But only thing is, if i use lot of data, then i am getting a run time error 6 "overflow". Please help.
 

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
257
replace the first two lines with these and see if it works
VBA Code:
Dim s$, c&, j, id$, dd, d() As String, sel As Boolean, folder$, fol As FileDialog, z, b$, i&, p&
Dim h() As String, t&, per$
 

Sunjinsak

Board Regular
Joined
Jul 13, 2011
Messages
143
Office Version
365, 2019, 2016, 2013
Platform
Windows
As a rule, you should never use type hints to declare your variables. They're only included in VBA for backward-compatibility and their use is strongly discouraged by most - including by Microsoft themselves!

Instead you should use the As clause to declare your variables. Taking the above declarations as example...

VBA Code:
Dim s As String
Dim c As Long
Dim j As Variant '<---- As this didn't have a type-hint assigned to it it will have been Dimmed as a Variant I believe. It should probably be of type Long or Integer though.
Dim id As String
Dim dd As Variant
Dim d() As String
Dim sel As Boolean
Dim folder As String
Dim fol As FileDialog
Dim z As Variant '<---- Again, should probably be of type Long in this case to prevent possible overflow errors but wasn't explicitly declared as such so I've left it as a Variant here
Dim b As String
Dim i As Long
Dim p As Long
Dim h() As String
Dim t As Long
Dim per As String
You can of course multi-line them as in the below example, but just don't use type hints!
VBA Code:
Dim s As String, c As Long, j As Variant 'etc.
It is also generally recommended to give your variables meaningful and descriptive names. It aids code readability a lot and, if done correctly, can often lead to 'self-documenting' code (i.e. code that doesn't need as many explanatory comments telling you what it's doing).
 

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
257
As a rule, you should never use type hints to declare your variables. They're only included in VBA for backward-compatibility and their use is strongly discouraged by most - including by Microsoft themselves!

Instead you should use the As clause to declare your variables. Taking the above declarations as example...

VBA Code:
Dim s As String
Dim c As Long
Dim j As Variant '<---- As this didn't have a type-hint assigned to it it will have been Dimmed as a Variant I believe. It should probably be of type Long or Integer though.
Dim id As String
Dim dd As Variant
Dim d() As String
Dim sel As Boolean
Dim folder As String
Dim fol As FileDialog
Dim z As Variant '<---- Again, should probably be of type Long in this case to prevent possible overflow errors but wasn't explicitly declared as such so I've left it as a Variant here
Dim b As String
Dim i As Long
Dim p As Long
Dim h() As String
Dim t As Long
Dim per As String
You can of course multi-line them as in the below example, but just don't use type hints!
VBA Code:
Dim s As String, c As Long, j As Variant 'etc.
It is also generally recommended to give your variables meaningful and descriptive names. It aids code readability a lot and, if done correctly, can often lead to 'self-documenting' code (i.e. code that doesn't need as many explanatory comments telling you what it's doing).
you are right, i did it out of laziness.
Thanks for that.
 
Last edited:

Rakesh99932

New Member
Joined
Feb 25, 2020
Messages
23
Office Version
2019, 2016, 2013
Platform
Windows
replace the first two lines with these and see if it works
VBA Code:
Dim s$, c&, j, id$, dd, d() As String, sel As Boolean, folder$, fol As FileDialog, z, b$, i&, p&
Dim h() As String, t&, per$

Wow, this worked well !!!. Thank you so much for prompt help. It will save lots of my time.
 

Rakesh99932

New Member
Joined
Feb 25, 2020
Messages
23
Office Version
2019, 2016, 2013
Platform
Windows
Wow, this worked well !!!. Thank you so much for prompt help. It will save lots of my time.
Please let me know if there is an option that the VBA will automatically take all Codes, instead of manually entering each.
This is just out of curiosity. But your first solution works like charm.
 

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
257
this should take all the codes
VBA Code:
Sub subsetTestAuto()
Dim s$, c&, j, id$, dd, d() As String, sel As Boolean, folder$, fol As FileDialog, z, b$, i&, p&
Dim h() As String, t&, per$
dd = 1
Set fol = Application.FileDialog(msoFileDialogFolderPicker)
fol.AllowMultiSelect = False
sel = fol.Show
If sel Then folder = fol.SelectedItems(1) & "\" Else Exit Sub
Do Until Cells(dd, 1) = ""
c = Cells(dd, 1)
j = ActiveSheet.Cells(1048576, 1).End(xlUp).Row
For z = 1 To j
If Cells(z, 1) = c Then
If InStr(1, b, Cells(z, 2)) > 0 Then
Else
If b = "" Then b = Cells(z, 2) Else b = b & "|" & Cells(z, 2)
End If
End If
Next z
d = Split(b, "|", , vbTextCompare)
For i = 0 To UBound(d)
For p = 1 To j
If Cells(p, 1) = c And Cells(p, 2) = d(i) Then
If id = "" Then id = Cells(p, 3) Else id = id & vbNewLine & Cells(p, 3)
h = Split(CDec(Cells(p, 2)) * 100, ".", , vbTextCompare)
End If
Next p

For t = 0 To UBound(h)
If IsNumeric(h(t)) Then
If per = "" Then per = h(t) Else per = per & h(t)
End If
Next

Open folder & "TXT_" & c & per & ".txt" For Output As #1
Print #1, id;
Close #1
id = "": per = ""
Next i
dd = dd + 1
Loop
End Sub
 

Forum statistics

Threads
1,089,515
Messages
5,408,729
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top