Forum Discussion

PaddyB's avatar
PaddyB
Brass Contributor
Dec 25, 2021

Import Excel and Sort by eMail-Domain

Hi all   merry XMAS to you all   I use the time to get some "long term wish" fixed but no luck at all - excel is not my "world" 🙂   my problem ist I get every month a CSV from one of our "Saa...
Excel
Formulas and Functions
bosinander's avatar
bosinander
Steel Contributor
Dec 30, 2021

I interpret 'lot of domains' as between 4 and 100 and assume that quite a few of them are new from month to month.
As described 26 Dec 2021 10:24 AM above, the attached file calculates the domains in the input addresses. Column A may be pasted or loaded via Data: (Get & Transform): From Text/CSV.

__/ Sheet3 has one of the domains entered into A1 and uses FILTER to fetch the addresses in that domain.

A3:

=FILTER(Input!A:A;IFERROR(FIND("@"& A1;Input!A:A);0))

__/ The first sheets;

  • Column A shows the sheets in the workbook
  • Column B shows the value in each sheets cell A1 (as chosen in C2)
  • Column H shows the domains that not yet have its own sheet
    It is calculated by comparing column B with hidden column F (press Ctrl+8 to show its [+])

Click the button to generate the missing sheets.

__/ The macro will generate sheets for each missing domain as follows:

Sub domains2sheets()
    While Not IsError(Range("domains_without_sheet").Value)
        Sheets(Sheets.Count).Select
        If Not Range("a1").Value Like "?*.?*" Then
            MsgBox "Last sheet seems to not be a valid template?"
            Exit Sub
        Else
            Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = "Sheet" & Sheets.Count
            Range("a1").Value = Range("domains_without_sheet").Value
        End If
    Wend
    Sheets(1).Select
End Sub

The defined name domains_without_sheet refers to =PowerPage!$H$2

Row 9 could just as well be 

Sheets(Sheets.Count).Name = Range("domains_without_sheet").Value

but would be misleading if cell A1 is changed.

 

Since it's just once a month, I think a paste and a click could be automatic enough.

It is also possible to select sheet4 and Shift+click the last sheet and delete them to only have the current months domains.

For this purpose or rather to get an overview of the number of items in each domain, I also added a counter in PowerPage!D:D. Minus one to exclude the sheets title.

 

Yea_So's avatar
Yea_So
Bronze Contributor
Dec 30, 2021

bosinander 

 

You might want to look into merge query "merge" using the join type anti-left or anti-right depending on which list is selected in the dialog box to isolate and create a new domains list where the current csv list does not match the current master list.

 

if the new csv get and transform list is selected as the first list, use join type anti-right to get the new domains list

 

if the new csv get and transform list is selected as the second list use anti-left to get the new domains list

 

after getting the new domains list, use the query merge "append" to add the new domains list into the master list

  • bosinander's avatar
    bosinander
    Steel Contributor
    Dec 30, 2021
    Yes, but let's assume that you get 60 domains whereof 40 are new and 35 not around this month. As long as the demand is to have the addresses gathered by domain on separate sheets, there will be a lot of manual work to load each new domain question to a separate new sheet and probably delete domain sheets not in use.
    I think the separation would be best done using more or less familiar worksheet functions and eventually combine them with oa few lines of code as mentioned above.
    However I like the for me new approach to consider merge (aka join) anti-left and anti-right. That may become handy when describing the functionality in yet another way 🙂
    • Yea_So's avatar
      Yea_So
      Bronze Contributor
      Dec 31, 2021

      bosinander 

       

      For me I tend to not separate data set unless there are certain processes that need specific data.

      Maybe the op needs to put some context on which process needs separate data set specific to the process.

       

      The best way to separate data set without using pq is the filter function for a specific process relative to the data set

Resources