export and save individual files from filtered dataset in turn

[mart10]

I am having to use Excel (2016) in order to resolve the following . Acces would be so much easier but the office will not use access

I have a tab of data where I need to

1, filter one column of data and then select each unique item in turn revealing the dataset and then save that dataset as a file, in all there will be about 60 files to save - basically the filter is looking at data per country and then each file would be sent to the person looking after that country. realise =this is not te best approach and Acces could do everything inc automated e-mail but I am dictated to be others

The running of the filter and saving process needs to run off a single macro Also the list of countries is variable in the dropdown list going forward

Can some suggest best Excel approach


PS to keep it simple the name of each file could be the filter value

 

[SkipVought]

Hi,

Clear as mud!

You usually use a filter when there might be multiple rows of the filtered value.

So how about a sample of the table and an explanation based on that sample?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[mart10]

Yes i know how filters work, what I want is for each selection filter in turn (via a macro) to output and a save a CSV file of that filtered data only and give the CSV file the filter name

eg In my workbook in tab in question called X

A B C (where C is the filter colum)
1 2 US
2 4 US
2 6 US
4 8 UK
3 1 UK
3 3 FR
2 7 FR

So when running the macro on data above it would in turn do filter selections on all possible values in column c and in case above create 3 seperate CSV file called US, UK and FR

eg US file would look like :
A B C
1 2 US
2 4 US
2 6 US

 

[SkipVought]

First, you'll need to be able to generate a list of unique values from column C. There are a number of techniques that can be employed to accomplish this: MS Query in Data > Get External Data..., Advanced Filter in Data > Sort & Filter..., or Remove Duplicates in Data > Data Tools.

Turn on your Macro Recorder and record the process of...
1) using one of those techniques, generate a list of unique filter values
2) assign one of those values to the filter
3) save the file with the appropriate file type.
4) Then turn off the recorder.

Post your recorded code for assistance in customizing your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Since we are getting into Macro / VBA territory, is your data (displayed in Excel) originally coming from a CSV (text) file to begin with?
I ask because if it is, you can write a simple code to:
[ul]
[li]Detect distinct values for Countries[/li]
[li]Read your original (base) CSV file line-by-line[/li]
[li]Write into separate CSV files records depending on the Country from base file[/li]
[li]Done[/li]
[/ul]


---- Andy

There is a great need for a sarcasm font.

 

[mart10]

the data lies in one tab of an Excel workbook

I cant write code, I will send the code tomorrow Skip

 

[Andrzejek]

Since you want to end up with CSV files "and give the CSV file the filter name"...
Save your Workbook (one tab) as a CSV file (in C:\TEMP\ in my example) and name it "BASE.csv":
[tt]
1,2,US
2,4,US
2,6,US
4,8,UK
3,1,UK
3,3,FR
2,7,FR[/tt]

Run this code:

Option Explicit

Private Sub [blue]JustDoIt[/blue]()
Dim strPath As String
Dim i As Integer
Dim strTextLine As String
Dim strCountry As String
Dim dicC As Object
Dim key As Variant

Set dicC = CreateObject("Scripting.Dictionary")

strPath = "[red]C:\TEMP\[/red]"

i = 1
[green]
'Pick distinct Countries
'and keep them in Dictionary dicC[/green]
Open strPath & "[red]BASE.csv[/red]" For Input As #1
Do While Not EOF(1)
    Line Input #1, strTextLine
    strCountry = Split(strTextLine, ",")(2)
    
    If Not (dicC.Exists(strCountry)) Then
        dicC.Add strCountry, i
        i = i + 1
    End If
Loop
Close #1
[green]
'Open each Country file[/green]
For Each key In dicC.Keys
    Open strPath & key & ".csv" For Output As #dicC(key)
Next key

i = FreeFile
Open strPath & "[red]BASE.csv[/red]" For Input As #i
Do While Not EOF(i)
    Line Input #i, strTextLine
    strCountry = Split(strTextLine, ",")(2)
[green]    'Write records to appropriete CSV files[/green]
    Print #dicC(strCountry), strTextLine
Loop
Close #i
[green]
'Close Country files[/green]
For Each key In dicC.Keys
    Close #dicC(key)
Next key

End Sub

You should end up with 3 CSV files in C:\TEMP\, one for US, one for UK, and one for FR


---- Andy

There is a great need for a sarcasm font.