MS Excel / VBA help thread

Column D

Any chance you could upload it here. I want to see if I can copy and paste it over.

=a1*1 didn't work for me on column D

the column with your pasted data. everyone will reference that as a and the first cell as a1.
because we're lazy.

the column with your pasted data. everyone will reference that as a and the first cell as a1.
because we're lazy.

only ask as it didnt work for me.

attached. new column in green

1 Attachment

• Data testing dates.xlsx

it's probably not as performant but I prefer the "apply a function, then add zero to the result" to get you a 1 or 0.

Ultimately though, this is 81 records with an error which could/should have been solved just by editing the cell with the error.
I think the question here is:
how do you edit cells without physically interacting with them?

would a replace work?

=REPLACE(A2,1,LEN(A2),A2)

yes. yes it would.

=TEXT(REPLACE(A2,1,LEN(A2),A2),"dd/mm/yyyy")

I did it without a formula, just go to the cell, press F2, then press return, and Excel will magically recognise it as a date.
F2 - return - F2 -return down the column, and Bob's your Uncle.

Fuckin' Excel.

I did it without a formula, just go to the cell, press F2, then press return, and Excel will magically recognise it as a date.

F2 - return - F2 -return down the column, and Bob's your Uncle.

Fuckin' Excel.

nope

Are you using Excel?

If bad data is in A1, what does

=mid(a1,1,1) return?

Do you mean =mid(a1;1;1) rather?

This is a regional settings thing. Set in the OS, at least on Windows.

UK, US default to comma separated lists (or formulae in this case). Most European countries default to semicolon.

PPT containing excel charts and links to those.
WHY THE FUCK IS IT SO SLOW TO UPDATE?

= 25-40 mins

One thing I've found is that the refresh is way quicker if you have the relevant spreadsheet(s) open, just in case they're not.

"YOU CANNOT HAVE THE FILE OPEN AT THE SAME TIME"

Is what my piece of shit tells me.

On a power bi note:
if there's a table you've created in there, and it looks nice and someone wants a regular export of it, can you schedule this to happen when you publish?

EDIT: I've resolved it by deleting and re-doing the pivot on Sheet 2. I assume I must have used the wrong data somehow, even tho I'm positive I didn't.

Struggling with pivot tables. I've only really used these in LibreOffice Calc where I had no problems.

I have two sets of data; 1) all the data, and 2) a section of the data between two dates from 1).

My workbook setup is:
Sheet 1 = All the data
Sheet 2 = two pivot tables; i) money in, and ii) money out
Sheet 3 = the section of data between two dates which comes from Sheet 1 originally
Sheet 4 = two pivot tables; i) money in, and ii) money out

When I created the Sheet 4 pivot, it gave exactly the same readouts as Sheet 2 pivots, even though I'm highlighting from Sheet 3. I then refreshed it somehow and it gave the correct readouts, but now the Sheet 2 pivot is giving the Sheet 4 readout.

I'm totally baffled and can't actually think what key terms I need to be searching for are.

Any pointers?

Cheers.

Without wanting to sound like a "suck these eggs grandma" type, it's times like this when it's good to use tables and names.

Plonk all your data on sheet one
go to cell a1. press Ctrl+T. BINGO a table is created. TABLES ARE MAGICK.
you can rename the table by clicking the table design tab in the ribbon, there should be a little window called Table Name. Write data in there.

Then when you want to create a pivot you can insert pivot and write the word data in the range.

I don't understand why you've used a sheet 3. You could just use 2 more pivots but set the range to the dates?

I’ve got some data on excel. In the columns it goes

Postcode. Name. Region. Location

What I’m trying to do is create a spread where you put any post code into a search bar and it brings up the info according to the post code.

I’m not great at excel and have had a try this morning without success.

you can probably vlookup this
so in a cell
=vlookup(a1, b2:e27310973091,1,0)

this means:
look up cell A1 in the table range b:e, return column 1, true match.

further:
if you have your postcode table in cell b2:e20000 (for example)

then in cell a2 write
=vlookup(a1,b2:e20000,1,0)

then type in a full postcode in a1.
a2 will return the matching postcode
you can then change the 1 to 2,3,4 to return the other bits of information you need.

Things you'll probably find:
your postcodes will need spaces removing.
it might be you only want to find a partial postcode - so FIND or SEARCH are good here
you might want to auto filter your data based on a cell, in which case you might want to convert your range to table

Are you looking at aggregates or individual lines?

If it's the former then a Pivot table might help.

It’s lines of data. I want to create a simple search bar where you put any post code in and then it brings up the Information

Have you tried the vlookup example I gave?
https://www.excel-easy.com/examples/vlookup.html

https://www.powerusersoftwares.com/post/xlookup-just-killed-vlookup-everything-to-know-about-this-major-new-excel-function

vlookup is still the best excel formula

Is there one set of information per Postcode? If so then @Chalfie approach of Vlookup is perfect (I'm more of an index\match, as you can then change the titles to decide what pulls through)

If you have more than one set of information per postcode, then it might be easier to use a pivot or a slightly more complex set of vlookups with a helper column.