MS Excel / VBA help thread

Are you uploading an attachment, or just the SQL?

By the forum?

That would most likely be because there's a block built in to stop people posting queries that could interact with the forum database

Comment out the code then try posting

relevant XKCD
http://xkcd.com/327/

/*SELECT stuff

FROM [A Cases 09 - 16]
WHERE [A Cases 09 - 16].[Date entered] Between #1/1/2015# AND #31/12/2015# */

/*FROM [B Cases 09 - 16]

WHERE [B Cases 09 - 16].[Date entered] Between #1/1/2015# AND #31/12/2015#*/

/*FROM [This month's cases]
WHERE [This month's cases].[Date entered] Between #1/1/2016# AND #30/04/2016#
ORDER BY [Date entered];*/

You could probably upload the files if you renamed them to .txt files first. I expect the forum will block vba, sql attachments

Seriously, this is like Ed and randos.

attachment is blocked. .txt file.
I get why. I really do.

Could you open the file on your machine, print it out, take a photo with your phone then upload the photo

I keep away from union querys as a rule. There must be some identifier that primates all of the year tables and i don't see why you can't join to all individually and do one select over the top. Need more info.

I've taken this to an email discussion. I can post the solution when it's there?
Not that you all care, but there may be other basic bitches like me out there.

I have a list of company names, about 10,000 companies. List A

I have another list of company names, about 300,000 companies. List B

I want to see which companies on List A are also on List B.

The issue I have is that the two lists are from separate sources so company names may not tie up exactly. e.g. random punctuation, Ltd rather than Limited, missing The or similar, etc

Any suggestions for a decent way to do this? I'm not looking for exact matches, just ones which are similar enough to justify looking more deeply.

Would this help?
http://www.excel-university.com/perform-approximate-match-and-fuzzy-lookup-in-excel/

Or this?
http://chandoo.org/wp/2014/12/15/compare-data-highlight-mismatched-letters-words/

Cheers, the second one requires the data to already be lined up but the first looks promising.

Is the following doable?

I use Time Recording Pro on my phone to keep track of where, who and how long for when I work. Work provides me with a Excel spreadsheet that is formatted to be printed out and filled in by hand or filled directly in on the desktop.

Time recording can export a excel file with everything on it. Is there a way to import it into the work spreadsheet so it autofills?

Can you make an intermediary sheet - i.e. one that takes your time recording output, and reformats it into the columns needed in the right order for the work spreadsheet. then you can just do a copy paste.

For example, if Time Recoder outputs:

Col A | Col B | Col C | Col D
Date | Time start | Time End | Client

and the work spreadsheet needs:

Client | Date | Total hourse

Then you can write a sheet that formulas off the time recoder sheet thus:

Client | Date | Total hours
= ColD | =ColA | =ColC - ColB

Any use?

Vlookups?
Macro to reorganise the time recorder sheet?

Yes, that's possible. Copy'n'paste could be a thing. Didn't occur to me that such a simple solution is feasible.

An automated version would be nice but I doubt IT/Admin will entertain it.

You'd need to explain that more. Just a simple engineer rather than IT geek.

I'd use a macro to automatically open the work Excel spreadsheet, copy the relevant data into the correct columns and then save it with that day's date.

Unfortunately you need to know VBA for this, although it's straight-forward enough to be a beginner's first project.

Do this:
Put a unique id in a new column of you tine recording work sheet. Put it one column to the left of the existing columns. So each row now has a unique id.
Go the worksheet your work wants you to use. Put a new column to the left of that.

Make it say a1 = a1 time sheet.

Then do a vlookup of this number so it returns the numbers from your time recorder in the order you want.

This is a quick and dirty fix.
The better option is to record a macro of all the steps you do to the time recorder sheet to get it in the right format as the work provided aheet and then saved.

Then post it here and have people mock your code and lack of skills in coding and then rip it apart and make it run in one line.

Which has worked for me.

In that case I'd skip the id column and use the INDIRECT formula along with ROW and COLUMN to tidy it up. (Am I doing this right?)

See.
I told you someone would get there quicker.

starts using indirect

Right. I'm trying to find duplicates in my access database.
People can be in this db multiple times, because they could have different infections at different times.
i.e
joe smith, has cyrpto in 2014, measles in 2015, and scarlet fever in 2016.

Sometimes, people make mistakes and enter data incorrectly so that
Jane Brown has crypto in 2014 and crypto in 2014.

How can I build a query to say:
show me all the people in the database with the same name, tell me how many times they feature in the database, and give me all the diagnoses they have?

Currently my query says
show me name, dob, first diagn, last diagn
group by name dob
having name >1

how do i get the second and third diagn?