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...
PaddyB Connect to the CSV file with PowerQuery (PQ), do some transformations, sort and load back to Excel. Sound easy! And it is, though, you need to learn the basics of PQ first. The link below would be a good starting point.
PaddyB Your choice. But I'd recommend to look into PQ anyway. Guaranteed that you'll find other uses for it that will save you huge amounts of time in other jobs you might be doing.
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.
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.
I would go with what Mr Van_Eekelen suggested because you set up power query once and it will repeat the process just by pressing the update all button, so if you setup a query by folder all you have to do is drop the new file into a folder then open the pq workbook and press ctrl+alt+f5 and it will update the workbood with the new data.
Yea_So IMHO Power Query (PQ) is a nice alternative when connecting to slow changing data since it demands a specific update event in Excel.
PQ also needs a completly different set of skills compared to worksheets but it may pay back by providing a bunch of nice features like unpivot. To get the transformed data, in this case email addresses sliced and separated by domain names to different sheets, PQ need to have a separate question for each domain. They can all be based on the same connection to the source file, but still need to be splitted into one separate 'question' for XXX.ch, another for XXX.de and a third for XXX.com since the wish is to have the result load to different sheets.
With PQ alone it is needed to prepare a sheet for each domain and manually manage (append a new question and a new sheet) whenever a new domain appears.
I do not see any smooth way around that except using a macro to loop through the months domains and setup a sheet for each one found. And macros seem to not be an alternative for this user.
Loading the csv file via the menu Data and command From Text/CSV and then use a worksheet formula like NowshadAhmeds ought to be a pretty good solution as above Dec 26 2021 10:24.