MS Excel / VBA help thread

You can put in where you want to start the find from. You know the location of the first character, so you can start from there.

Nested finds

Yeah it was the 3rd "\" that was tricky, as that varies in position, managed it with nested substitute. Textsplit would have been handy but I'm on office 2021.

Anyway, I have loads of entries for each client and project, each corresponding to a file, with a file size for each. Sorry if this is quite basic, but how can I make a chart showing the total filesize for each project? A nice-to-have would be subdivisions for filetype or project. As I understand it I might have to go via a pivot table?

Yes. Pivot is the easy answer.

What you should do as well is add an additional field called "count". Make the value in this = 1 for every row. You now have something that will consistently provide you a thing to sum/count.
Making it easier to do "tell me how many files i have for project x" and "what is the average file size for project y" in a pivot table.

This should be an easy one for you lot, but I have been struggling.
I'm trying to write something which counts the number of times a particular word appears somewhere within a string. plsthanks

A quick and dirty solution, first thing that sprung to mind:

Find the length of the string, find the length of the string without your word, divide that difference by the length of your word:

Where A1 is your string and cheese is your word (can obviously point that at a cell instead):

=(LEN(A1)-LEN(SUBSTITUTE(A1,"cheese","")))/LEN("cheese")

I have some data (about 50k rows), I want to make some graphs from it and publish online somewhere.

I want people to be able to intuitively change a couple of the filters on the graph.

What's the best service for this? Data is public domain and it is personal not work. I would like it to be free or incredibly cheap. Cheers

Bi
Tableau
Your own shiny R space on github ? https://shiny.posit.co/r/getstarted/shiny-basics/lesson7/

Thanks for the advice about Pivot tables / renaming to those that chipped in. Now pretty much got what I want (will figure out how to dynamically link to a CSV some other time).

But one thing I can't seem to get working is the value filters. Manually selecting entries works, as does slicer, but a 'top ten' or 'greater than' filter in the Size field doesn't seem to have any effect.

1 Attachment

I think if you want to filter the top 10 largest by size you need size in either the rows or columns, and filter it there.

ah got it, I needed to apply the filter in the chart itself, not the fields section

Fwiw I solved my issue by

1. Concating the id with the other field which should match in a pair,
   
2. remove duplicates to leave only the mismatching pairs
   
   

Ignore. I'd discovered the advanced filter under data.

Cheers, Tableau was the answer here. Free licence for PowerBI doesn't allow you to publish online and R is on the long list of stuff that I should learn at some point.

I've got some data that I'm looking for patterns in to troubleshoot.

There are c.5 fields I'd normally use for a steer. Maybe up to 10 as I look deeper. I can do this either with a pivot or often just need a quick check of the filter.

However, in total there are 50 columns and I'm wondering if there might be something in those other 40 columns (more likely 30 as some definitely don't factor).

Is there some low effort trick to get a quick view of trends? Idk off the top of my head say if all of these tasks were triggered between a certain time.

It sounds like you need a reporting dashboard.

That would be good. There is a tableau... but unfortunately the issue is that tasks aren't making it to the system that the dash uses 🫠

I was trying to think if I could use a pivot and line graph to see if there is anything, but can't quite think how to do it in a useful manner.

Have you thought about sparklines?

You'd have to get your data into a table with a row for each field?
https://chandoo.org/wp/excel-sparklines-tutorial/

Alternatively, which of the fields are dependent, create a sheet/page with small tables (either by pivot or powerquery) of each of these fields and then chart off them.
charting from multiple pivots is a right twat though.

Powerquery - I have a list of items within categories, which are sorted within the categories by a category specific logic (e.g. items in Category A might sort by start date ascending, Category B by start date descending, etc.). That bit is working fine and I end up with a table like:

Category Item Rank
A Item X 1
A Item Y 2
A Item Z 3
B Item C 1
B Item V 2
B Item B 3

I've been asked to group certain items together by exception based on purely subjective criteria, something like in this case Item Z should appear after Item X rather than item Y. At present there is only one exception but potentially there would be more than one (but never a large number)

What is the best way of achieving this?

It seems a bit of a strange requirement but I'd guess the best way would be to create a list of your custom sort order and use that as the criteria with Table.Sort e.g.

 Table.Sort( 
  Source,
  { each List.PositionOf( {"Item X", "Item Z", "Item Y"}, [Item] ), 
    { "Category",Order.Ascending } } 
)

(you can reference a list instead of doing it manually).

Or you could have another table with your sort order and an index. Merge the tables and sort by the index.

Just joined the Logitech Master MX cult.
Getting a dinky carry case from AliExpress to take it to work it's that good.

Getting a bit stuck. Basically my VM can't handle my sheet long enough to finish the formula. What terms do I need to search for/guide on how to do this in PQ?

In sheet 1, column D
xlookup($A2, 'sheet2'!$A:$A, 'sheet2'!$J:J)

then do that for a billion rows down Column D

then repeat a similar formula for column E to return a different column in Sheet 2 and so on.

Or is there a vba or something? Once I've got the correct data all layed out I just want to lock it in and paste it as values so I can get rid of all these formulas.

is sheet 1 loaded as a table and as a connection only? same with sheet 2?
Ideally, I think - happy to be wrong,

solution 1 :these should be separate tables in a data location then you could have a third table just doing it all by lookup @aggi has a lookup solution i've used from here but can't find.

solution 2 : you have a unique key for each row? and then just merge on this.

If it's this big shouldn't it be done in flow/automate?

(is it really a billion rows? why?)

= Table.AddColumn(#"Source", "NewColumn", each try Table2{[z=[a]]}[x] otherwise null)