MS Excel / VBA help thread

Ah actually I think I've mis understood. The form is for me to enter the data not not for my colleagues to submit the data to me.

I'd use MS Forms for the data capture then a Power Automate flow to append it to the spreadsheet.

I've got a CSV file that is semi-colon separated but also has commas in the numbers.

If I just try and open it in Excel it assumes it is comma separated and screws up.

In years gone by you could import a text file into Excel and choose the delimiter. Now I can only find the option to do that if I import it into power query and I don't want to do that as I'll need to clean all the file. Any idea where the option to just bring a text file into Excel has gone?

This happens to me about once a month (different files) and I bumble around looking and then give up.

I had this the other day. You can paste the text into Excel and then do 'text to columns' and specify the delimiter.

Or Data --> From Text/CSV, which imports data from an external file as a query.

I didn't understand why you don't want to use Power Query, as you can clean the data in that, but Get Data does create a 'query' underlying the table.

I was going to suggest this. You can import the data from the csv and then do text to columns and pick the ;. That should preserve your values with commas in them.

These days I format all my tables using some scripting language, so not sure exactly where these options are buried.

Yes, that's what I ended up doing but the open in Notepad (or something else if it's got characters notepad can't parse) then copy/paste into Excel then text to columns just seems like a backwards step from open directly in Excel and choose a delimiter.

I just want to see what the files are to start with so don't want to go to the effort of changing locales, removing errors and things to get a decent output from PowerQuery. Often a quick look and I'll realise I'm never going to need them.

I'm trying to return results using FILTER with two criteria and getting in a muddle.

What I want:

When 'unique id' = cell BK1 and 'location' = cell bk2, then show me all the 'products' which match that criteria from sheet 1, column h

CHATGPT has saved the day (with a bit of editing)

=FILTER(Sheet1!$H$1:$H$1000, (Sheet1!$A$1:$A$1000=Sheet2!BK1)*(Sheet1!$B$1:$B$1000=Sheet2!BK2), "")

It only just occurred to me to tell it to have another crack using the FILTER function.

The reason I'm using FILTER /want it this way, is so I can look at a table with the UID and location at the top, a long list of products underneath, then pick out good combinations of products to run tests with.

This is already a short list.

Yep * does an AND condition and + does an OR condition with FILTER.

You have to be careful to encase each condition in brackets (easy to forget as when you use a single condition it doesn't need to be in brackets).

I think that was my problem. I struggled to get it working following the guides.

I have two columns in a sheet that I want to make a chart from; dates and names.

I want to display the number of names for each month - i.e.

01/03/2024 / Donald
29/03/2024 /Mitt
31/03/2024 / John
31/05/2024 / George
28/06/2024 / Bob
31/12/2024 / George
31/12/2025 / Ronald
29/12/2027 / Gerald

Gives me

Mar-24 / 3
May-24 / 1
Jun-24 / 1
Dec-24 / 1
Dec-25 / 1
Dec-27 / 1

(I actually want to put some other stuff in the graph, but that doesn't matter for now)

These names and dates may change over time, so to me it seems to make sense to have a 2nd table with a list of all the months between my first and last date, and then a formula to return the result. Then set the chart settings to ignore null months.

Initially I wanted a pivot to do it which I could refresh, but the problem is, that if I add 'Months' then it will count 3 for 'Dec', not 1 for Dec-24, 1 for Dec-25, 1 for Dec-27. If I do it by full date, then I still need a way to do the count, so what is the point of adding a pivot chart to the mix?

Questions are;

1. Is there a pivot trick I am missing to give me what I want?
   
2. wtf is going wrong with my formula that is returning the incorrect count for counting Jan-24?
   =COUNTIFS('sheet1'!E3:E52, ">=01/01/2024", 'sheet1'!E3:E52, "<=31/01/2024")
   
   
   

Cheers

Does there need to be a SUMIF in there too?

You can pivot this.

You'll get a date hierarchy inserted into the pivot.

Dates in col a
Name in col b

Make pivot
Pivot will have Date and name in the field list
Also Months, Quarters, YEars.

Chuck the date (or months/quarters, years) in rows, then count of name in the values.

Alternatively, col c = month

=Countif(c:c,x) where x = month number

or even

=SUMPRODUCT(1*(MONTH(A4:A11)=3))

But then I end up with a count for each date rather than month.

Let me try this.

On the pivot?
Look in the field list?
You should have three new fields under the date, depending on your version of excel, as excel now inserts date hierarchy in there, giving you
Quarter (date)
Month (date)
Year (date)

How do I easily get a dialogue box in VBA with custom buttons. e.g. this

but the buttons have different text?

I've found this horrendous solution https://wellsr.com/vba/2019/excel/create-advanced-vba-msgbox-custom-buttons/ but it strikes me that there should be something easier.

You should have three new fields under the date,... as excel now inserts date hierarchy in there, giving you
....

Month (date)

But for some ridiculous reason it counts all the Dec. Annoyingly my data has most months in 2024, and then Dec 2025-27. So as per my eg, I'd still get 3 for Dec. Not 1 for Dec-24, 1 for Dec-25, etc.

Maybe the answer is to break it into 3 pivots, one for each year or something.

No, you just need to add Years into the pivot along with Months.

If for some reason you’re not getting date hierarchy (old release or online) then split the date into new y m d columns in your table and then pivot this modified table. Then you’ll have the data you might want.

https://www.contextures.com/exceldategrouping.html

Date hierarchy was added in 2016.

How big is the data set?

I work on Residential Mortgages Backed Securities and yeah, queries like the above is just done on a spreadsheet without the need for Pivot tables.

50