Import Excel and Sort by eMail-Domain

> find a solution what more or less is working

Hi PaddyB 

I have combined a few things into something attached that could be suitable for you.

Sheet Input;

Column A holds the email addresses and F2 visualizes the different email domains from A2 and down.

Sheet2 and 3 holds the email addresses from two different domains.

Similar to NowshadAhmed suggestion, each of those sheets shows the mail addresses related to the chosen domain.

The selection list shows domains not already used on another sheet.

Since xxx.com is selectable, there is no sheet for that domain. Copy Sheet2 (right click the tab of Sheet2: Move or copy: [x] Create a copy and (move to end)) and select xxx.com in A1.

Sheet PowerPage has in column A a list of the workbooks sheets. They are also linked to respective sheet.

If you'ld like to know more about how the automatic sheet list works, you may read about version 4 macros at https://exceloffthegrid.com/using-excel-4-macro-functions/.

Column B shows the content from cell A1 in each sheet using the function INDIRECT.

F2 lists the unique domains using

=LET(
  data;    OFFSET(Input!A:A;1;0;COUNTA(Input!A:A)-1;1);
  output;  UNIQUE(MID(data;FIND("@";data)+1;4711));
  output
)

H2 lists the domains that are missing an own sheet;

=FILTER(F2#;ISERROR(XMATCH(F2#;B:B)))

Finally, cell A1 at the different domain sheets have a data validation list that suggests any missing domain, listed in H2 with its spill area (H2#)

Instead using F2 would be like NowshadAhmed suggested.

-/-

The file attached contains version 4 macros and has to be saved as xlsM.

To activate them it seems nowadays necessary to include a VBA macro to get the enable question.

To get the sheet list updated, you may have to save, close and repoen the file (with macros enabled without question).

The VBA macro (that trigs the yellow enable question bar) is also a shortcut to activate the index sheet/first sheet.

Sub activateSheet1()
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    ThisWorkbook.Activate
    Sheets(1).Select
End Sub

Finally, try Data: From Text/CSV to connect to the csv file could be worth the while as a first step to using Power Query. You will get the csv data into a new sheet and you just have to cut that new table and paste it at cell input!A2 to have it all quite automatic with Ctrl+Alt+F5.