Please comment on what each row is about to do

These are the sheets that this code should refer to PLUS sheet 20 which hold all of the data

Is there a problem with the coding ??


Sheets 4 to 17 inclusive

Please Login or Register  to view this content.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


If possible I would like a BRIEF description as to what each line ( If possible) is about to do, so I can learn more about VBA...

Please Login or Register  to view this content.

Your code will loop through values VK3, VK4, VK5 ... VK15, VK16, VK17, applying a filter on that value, then copying filtered values to a similarly named worksheet. However, not all those sheet names exist, according to your post....

And what would you like me to do with it?

I don't understand how you're identifying which rows need to move to sheets "VKATV", "VKDStar", VK23cm", etc.

I can't see any values in column Q to assist with identifying the target sheet.

More broadly, I don't understand why you want to split your data table onto multiple worksheets...

Hi Olly


I have attached a copy of what the different modes are coloured column " Q "

All but Yellow are to be sorted and go into their own sheets.

The yellow panel gets sorted and goes into "VK1", "VK2", "VK3", "VK4", "VK5", "VK6", "VK7", "VK8".

The other colours go into VKATV", "VKDStar", "VK23cm", "VKDMR", "VKC4FM", "VKP25"

If it was possible write code so that the wicen rows be moved followed by the

"VK1", "VK2", "VK3", "VK4", "VK5", "VK6", "VK7", "VK8", VKATV", "VKDStar", "VK23cm", "VKDMR", "VKC4FM", "VKP25"


Here is the wicen code

Please Login or Register  to view this content.

If it is of any help I could highlight the various blocks and give them their own names eg: VHF, VKATV", "VKDStar", "VK23cm", "VKDMR", "VKC4FM", "VKP25"

The only thing I cannot block is the WICEN call signs as they are scattered throughout the rows, that is why we move them first.

Peter

You're not really understanding my question.

You've provided a MANUAL answer - but how do we turn that into a PROGRAMMATIC calculation of how to determine where to put data?

There's nothing (that I can identify) in your source file to inform us that data in row 552 should go somewhere different to data in row 3.

If you're going to manually colour-code or label data sections first, then you may as well just manually move the data. I get the impression you want to automate this as far as possible, to repeat for additional datasets - so we need to determine the algorithms, to decide how to filter data according to your requirements.

I'll ask again, though, as I suspect there may be a more efficient method of doing all of this - what's your purpose for splitting the data table into multiple worksheets?

Sort of.

With a bit of digging into it...

I get that you can assign group WICEN by the presence of "WICEN" in the Service Area. I get that you can group remaining callsigns by wavelength & technology, with group names for 70CM - 10M FM determined by the initial 3 characters of the callsign. So now we can programatically assign a group to each row of source data, based on the identifier in column R (Notes) above each logical grouping of rows.

So far, so good.

Please allow me to come back to the same question one more time, though, before providing a solution. WHY are you splitting your data into multiple worksheets? What is the aim / outcome?

See, I'd be more tempted to load the entire dataset into a model, with a group assigned to each row, then query / report on that model as required.

I still don't understand the need to have lots of separate worksheets.

Have a look at the attached. This queries the data from your "temp" worksheet (based on the file location specified on the Parameters sheet), and summarizes it in one table, with a slicer to select the group. The groups have been determined using the previously discussed logic, and the Maidenhead location updated.

I don't understand what you're aiming to achieve, with your drop down box on the "Master" sheet - but now we have all the data in a model, so it's easy to create additional views / queries against it.

In terms of automation, you can update the source file name on the parameters sheet, then simply "Refresh All", and you have your latest data updated.

For reference, here's the queries used to get and shape the data:

SourceFileName:

Please Login or Register  to view this content.

VK Data:

Please Login or Register  to view this content.

Maidenhead:

Please Login or Register  to view this content.

Olly has used PowerQuery for the VK data. Click on the Data tab and then Queries & Connections - you will see the queries he has set up on the right.

Nice one, Olly!

1. Change the filepath / name on the Parameters sheet, to the file containing your updated data, then simply click Data > Connections > Refresh All. The query looks for a worksheet with a name beginning "TEMP Directory" in the named file, and updates data from that sheet.

2. The 'code' is queries (Power Query, named Get & Transform Data in Excel 2016 onwards): click Data > Get & Transform > Show Queries.

3. Wicen group will be created, when there are values including "Wicen" in the "Service Area" column. The data you posted earlier already had the Wicen data removed, which is why this group dosen't exist in the file I uploaded. Perhaps you can post a sample file of your actual source data, the way you receive it?

4. The data source sheet is in the separate file, which is reference by the query (see point 1).

5. Simply change the number formatting of those table columns.

6. Maidenhead is already formatted to 8 points. Did you mean you wanted 10?

7. The easiest way to change the sort order is to add a custom list. Here's some VBA code to add the list - you only need to run this once:

Please Login or Register  to view this content.

See attached file for updates.

You can do the custom sort order in PQ as well: https://stackoverflow.com/questions/...ing-powerquery

Originally Posted by AliGW

You can do the custom sort order in PQ as well: https://stackoverflow.com/questions/...ing-powerquery

That won't change the display order in a slicer though - which is what we wanted in this case..

Okay - it looks like you've already done some sort of manual manipulation to those files?

We could quite easily combine them in a query. But better to work with your actual raw, source data, to eliminate any requirement for initial manual processing.

Can you post your actual RAW source data?

Ignore this post - you've since posted a CSV file. See my next post for a CSV version.

And here's a version which picks up data straight from the CSV file:

VK Data:

Please Login or Register  to view this content.

You can name the CSV file anything you like - and when you get an updated dataset in CSV format, simply change the filename parameter, and refresh all data, to update the report.

?

Look at VK Data v1.3xlsx atachment, in my post #23.

There are indeed 56 WICEN rows. Don't worry one bit about row numbers, they're completely irrelevant. We could change the sort order of the query, and the actual row numbers for each subset of data would change again.

EDIT: You do understand that all the data is being presented in one table, on one worksheet, and the slicer at the top of the page is merely filtering that table?

Olly MANY MANY THANKS

I dont want to sound ungrateful can you code th esheets in this order please..

"VK1", "VK2", "VK3", "VK4", "VK5", "VK6", "VK7", "VK8". "Wicen","VK23cm", "VKDStar", "VKP25", "VKDMR", "VKC4FM", "VKATV",


I like to be able to reward you somehow.

Peter

EDIT I found that I can't use my scroll wheel on the mouse in the sheets.

To change the order of the slicer, simply edit your Custom List (File > Options > Advanced > Edit Custom Lists)

Or run this VBA code once:

Please Login or Register  to view this content.

Happy to have helped. I'll feel rewarded if you feel you've learned something new about how you can use Excel

What do you mean, "side to side in the screen" ?

Are you asking how to change the zoom level? (slider in bottom right corner of Excel window)

To update from a new / different CSV file, just enter the CSV source file path / name into cell C2 of parameters sheet, then click Data > Connections > Refresh All. It will take a moment or two to refresh.

Olly

I cannot for the life of me get the file to update from c:\downloads\Repeater Directory 06-06-19

I enter the correct address into cell C2, which comes up in the formulae bar


Error message attached







Ohhh Do you follow Rugby League, tonight we have our " State of Origin" , that's between Queensland and New South Wales.

That error happens because the default setting for Excel Power Query is to consider "Privacy Levels" when combining data.

You can manually go to the Power Query Editor
(Data > Get & Transform Data > Show Queries, then right click a query in the Workbook Queries pane, and select Edit)
then choose File > Options and Settings > Query Options > Privacy > Ignore the Privacy Levels and potentially improve performance

Alternatively, run this VBA code once:

Please Login or Register  to view this content.

olly

Sorry I been away watching our big footy game ( We won 18/14 our way.)

Can the program update by looking at a xlsx file please.

I have cleared the error message but now it says it can't find "call " in the database.

.

That suggests that the data structure of your new source file is different... Can you confirm that the column headers of the 'new' CSV file are the same as the previous CSV file?

Perhaps you can upload the 'new' CSV file, so I can test it?

Okay, that CSV file is a different format - it's tab delimited, not comma delimited.

So you can change the VK Data query to:

Please Login or Register  to view this content.

This will handle either comma delimited or tab delimited source files.

Hi All

When I upload this code my program stopped working see the attached file,


The code below is the original coding, NOT understanding how it all works I must seek help from others.

Please Login or Register  to view this content.

Regards


Peter

It works here, Peter. Did you refresh the query? Did you check the privacy settings (post #33)?

It looks like you've done everything right. The file you uploaded in post #38 works perfectly with the CSV file you uploaded in post #36. See https://youtu.be/6GBvgrrsuXc

You are using the same CSV file, right?

Okay. It was only demonstrating that I can't reproduce your error, using your files.

It's almost like you're not reading my posts...

The file you attached in post #38 works.

It works with the CSV file you attached in post #36.

So I can only think that you don't have the intended CSV file located correctly in C:\downloads\Repeater Directory 06-06-19.csv

Attachment 627286

? It's only a screenshot of the correct CSV file in the intended folder.

https://postimg.cc/PCVXyp8f

As long as you have the CSV file from post #36 saved as C:\downloads\Repeater Directory 06-06-19.csv, and as long as you're using VK Data v1.4.xlsm (as attached to post #38), then it does work.

I understand you are frustrated. I'm doing all I can here to get this working for you.

Can you please confirm that the CSV file that you attached to post 36 is IDENTICAL to the file stored in C:\downloads\Repeater Directory 06-06-19.csv ? (I mean identical - the very same file, the very same filename).

Olly

Please confirm that I use C:\downloads\Repeater Directory 06-06-19.csv , when that is in the formulae bar I press enter
then the DATA tab comes alive, and in the connections tab I click on I press on the "Refresh All ".

Then I check the output page only to see that there is nothing there. Then I click on the Clear Filter and the screen fill with the WICEN data only NO other tabs


I have used the files off sheets 36 and 38.


Screen shot Doc attached

So I have no idea what has happened here I am using the VKdata v1.4.xlsm downloaded of one the sheets here..


Peter

Peter - please follow these steps exactly:

1. Download file test1.csv attached to this post, and save as C:\downloads\test1.csv

2. Download workbook VK Data 1.5.xlsm attached to this post. Open this workbook.

3. Click Data > Connections > Refresh All > Refresh All

4. Wait a moment.

Please follow ONLY THESE DIRECTIONS, and report back what happens.

Originally Posted by Potholes

just before I do that do you use a program " Teamviewer"

Not from this office, I'm afraid...

Please Login or Register  to view this content.

What exactly does this mean? Is the query producing any results at all?

..



Dear Alli, Olly and any Reader.

Thanks go to "Sintek ", for all the work on the project before my system crash, then "Olly" stepped up to the plate, and has put in many hours educating me
in a different method of programming other than VBA.

I have attached ( vkdata v1.3a.xlsm), this file has everything in it that I require, however I cannot get it to update, refer to the attached Document.
Previously the .CSV and working files resided in (C:\Download\) directory, HOWEVER I now have a permanent directory for the project, ( C:\Radio Data\ ) ,
as before everything was in my Downloads dir with 100s of other files and it was taking too long to navigate too long so I created the new dir.

Attached is a file called ( Repeater & Wicen 07-06-19.xlsm) it show the database MINUS the wicen data which is on the Yellow Wicen page thanks to the
macro on the Repeater Directory sheet.

This Wicen data is very very essential as the Australian Amateur Radio hobby has setup many radio station both fixed and mobile, some are only accessable ,
by 4wd vehicles, these stations are activated in the event of a major event, Floods, Bush Fires, Cyclones, the station would be activated when any of
these situation arise, what they do is to supply comms in areas where normal comms are not available, and pass messages to the Authorities.

Olly's latest version ( vkdata v1.5.xlsm) is still working on me being able to being able to access and upload the source .CSV file, Someone believes
I must have run over a " China man " because they are stopping me achieving our goals.

I have attached the original database called ( Repeater Directory 31-05-19.xlsm ) which is nearly 5mb, so I have zipped it down to 3 workable Files
Book1.zip, Book2.zip & Book3.zip.

Last night (6/June/19) Olly sent me 2 files to work with (Vkdata v1.5.xlsm ) & ( Test1.csv) to test that I can link the program to the .csv file , yes it
worked with some exceptions. unlike (vkdata v1.3a.xlsm) vr 1.5 is missing the tabs below, only the wicen tab was visible.

( VK1, VK2, VK3, VK4, VK5, VK5, VK7, VK8, Wicen, VKATV, Dstar, VK23cm, VKDMR, VKC4FM, VKP25. )


Further Files on sheet 62


Regards Peter

only the wicen tab was visible.

Morning, Peter!

I am looking at VK Data v1.3A and I do not understand the comment. There are two tabs: Parameters and Output. There is no WICEN tab, and in this file there is no VBA.

Across the top of the Output sheet is a slicer - clicking on each of the categories reveals the filtered data for that category, and clicking on the filter icon top right of the slicer cancels the filter, revealing all of the data.

Attached is a file called ( Repeater & Wicen 07-06-19.xlsm)

Attached where?

EDIT: Just appeared!

EDIT AGAIN: No, that file isn't there ...

If I try to update the workbook with the Repeater Directory 06-06-19,csv file, it is reporting that a column named Call cannot be found - this suggests that the layout is different.

Just seen v1.5 - working with that and updating it with the 06-06-19 CSV file works perfectly.

Here:

C:\Users\Alison\Downloads

You need to change the source path in the parameters sheet to reflect the correct location. Make sure you don't have that extra space in the file path after the final slash:

C:\Repeater Data\Repeater Directory 06-06-19.csv

Are you sure that the directory is really in the root? Could it be that the real location is something like this:

C:\Users\Peter\Repeater Data\Repeater Directory 06-06-19.csv

That's fine, Peter, so now please check that you don't have that extra space in the file path. It should be this:

C:\Repeater Data\Repeater Directory 06-06-19.csv

NOT this:

C:\Repeater Data\ Repeater Directory 06-06-19.csv

Originally Posted by Potholes

Hi Ali


Is this the only code or is the more hidden somewhere please..

Ignore that comment for now, please. Let's just get the file path sorted out. One step at a time!

Administrative Note:

Please don't take anything to private message, as per the forum guidelines - thanks.

Peter - you need to user a .csv file, not an Excel workbook. The code is designed to work with .csv. Olly may be able to change it to work with .xlsx, however it did seem earlier in this thread that the directories were coming to you in .csv format, so I'm not sure why they now need to be .xlsx.

OKay - my summary of the requirement, and development:

You have a requirement to transform source data, by determining a group based on non-repeating header text in your source data, then present this data by single selectable group.

Through several iterations, we established that your SOURCE data is provided to you in CSV format. You have shown two different structures of CSV file (one comma delimited, one tab delimited.)

I have provided an Excel workbook which uses Power Query to import and transform your data from the source CSV file (in either format). This file is VK Data v1.5.xlsm. This file will import data from either of the two CSV file structures you have provided.

The Excel workbook requires the source CSV file path and name to be entered (precisely) in Cell C2 of worksheet [Parameters].

If you are experiencing problems importing and transforming data using this workbook, then the problem can only sit with the source CSV file:

Either: The file path / name is incorrect, which means Excel cannot accurately locate the source file
OR: The source file is in a different structure or format to the two CSV files you have demonstrated, to date.

Can we keep the scope of this limited to resolving the issue with importing the CSV file, right now? If we keep changing scope without resolving issues, we lose track of requirements and progress.

So are you saying that (a) you are now getting the right results with the file path and (b) that you are happy for the source file to be a .csv?

Please can you be very specific when giving information?

Originally Posted by Potholes

I am satisfied with the update function now.

So, this is now working as you wanted?!

Can you please share the .csv file you used? When I updated v1.5 with the 06-06-19 .csv this morning it was working perfectly.

Earlier on today I put a lot of info in sheets 61 and 62 which happened while ALI was talking to me so I could bring her up to date.


I still cannot get it to appear as is in V1.0 That one gets an error if I try to update says something about different version not compatible

Peter - you are not making sense.

Why are you working with version 1.0? Please use version 1.5.

Where (in which file) are sheets 61 and 62? Again, I have asked before: please be SPECIFIC!

Ali

My reference to V1 is only to the group box at the top of all the data eg-

VK1 VK2 VK3 VK4 VK5 VK6 VK7 VK8 WICEN VK23CM VKDSTAR VKP25 VKDMR VKC4FM VKATV


At the moment in v1.5 it is only showing wicen and blank


I have attached v1.3A ONLY for the purpose of what I am missing off V1.5.

I am NOT using V1.3A it is just in the Basement with other files.


I trust this clears up my messages.

Right. Stop. You're being REALLY confusing. Please follow these steps.

1. Close ALL files. Shut everything.

2. Open workbook VK Data v1.5.xlsm

3. Select worksheet [Parameters] then double-click the grey cell (C2).

4. Select the appropriate CSV file and click Open.

5. A dialog box will open prompting you to refresh data. Click "Yes".

6. Wait a moment.

7. Post a screenshot of the Output sheet.

8. Post a screenshot of the Parameters sheet.

...


I have done as requested here is the

Olly.Docx

Progress.

Okay, that shows that data has loaded, but the Wavelength and Technology fields have not populated correctly.

This is almost certainly down to a different source data structure than expected.

ACTION: Please attach file C:\Repeater Data\Repeater Directory 04-06-19.csv so I can test that.

That is a different file structure than the previous CSV files you attached! Can't you see that it is different?!

When importing data, the file structure is CRITICAL. Excel can't interpret meaning of a file, it relies on a consistent structure to locate the required data.

QUESTION: before I spend some more time updating the query to handle the different file structure, I want to understand WHY you have several different CSV file structures. Is it because you receive files in different formats? Or is it because you have made changes to the CSV files yourself?

(I can't believe it's taken us 95 posts to actually identify your true source data!)

Olly

All I have done is to add a new column "A" Copy the NOW COLUMN "C" over so it makes it easier to search for a Call sign, then format the NOW Column " B & C" out to 4 decimal points, shift column "J" to the right 1 column and Name " Maidenhead".

If I STICK to this regieme can you work with it, OR what is your preference.


You say it and that will be the way I will run with it..


PS The far right column NOTES is NOT referred to in anyway

We agreed earlier on that a key objective here is automation - so let's remove ALL the manual steps, and get the data direct from source.

The attached file queries the web source directly - and allows for updated filenames. So when you click Data > Refresh All, it will get the latest data from the website. Note that this will overwrite any manual changes you have made to the data - so make sure you keep dated copies of files with any changes made.

Here's the new query:

Please Login or Register  to view this content.

Query Maidenhead is the same as before.


Remember - all you need to do is open this file, and click Data > Refresh All.

...
I am getting an error



We couldn't refresh the connection ' Query- VK Data',here's the error message we got:

[Expression.Error] The import Text.BetweenDelimiters Matches no exports.Did you miss a module reference?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

This error comes up if I go to

Data\Show Queries then Workbook Queries Right click VK Data then Edit ( 797 rows loaded ) no errors shown here

This is what comes up after I clicked on the edit

Expression.Error: The name 'Text.BetweenDelimiters' wasn't recognized. Make sure it's spelled correctly.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Is this where you go to for the data bearing in mind the date on the end will change.

HTML Code: 

It takes me straight to the CSV file

>>>>>>>>>>>>>>>>>>>>>>>>>>

Is the end product going to give me these headings up in the Group box please.. ????

VK1 VK2 VK3 VK4 VK5 VK6 VK7 VK8 WICEN VK23CM VKDSTAR VKP25 VKDMR VKC4FM VKATV

>>>>>>>>>>>>>>>>>>>>>>>>


Pete

Did you simply open file VK Data v2.0.xlsm ?

Once again, I am unable to recreate the error you describe. Please send a screenshot of the error.

The query makes allowance for the date in the filename changing - the first 3 lines of the query find the CSV filename.

Here as requested


I forgot to mention that I get this error when I go to Edit the panel on the right and select the VKdata

Weird. I can't recreate that error at all. Try switching everything off and restarting, then try again.

The "newer version" message shouldn't cause any problems.

How do I find out what version of Excel I have 2016, I don't know what version it is.. ????

Don't worry about that just now. Just switch everything off and back on and try the file again.

Still no joy see new attachment