MS Excel / VBA help thread

Thanks so much! Thats perfect, managed to throw a few more automatic parts to that section too

Right. I have a question. Does anyone have any experience of SelectSurvey?
Apparently, it's T_SQL based so I should be able to do stuff in it.

I'm trying to generate a matrix based on information in a spreadsheet in the background.
I've tried asking the company for help, i've got so far before they said "hey, this is complicated you need to pay us!"

Something like =(vlookup,a1,f:g,2,false) where cols f and g contain
team and manager table and a1 has the team.

But better to put the managers table on a separate sheet

I have a load of projects, each assigned to one person. Each project has 5 distinct phases, the end of one phase is the start of the next. When I'm resourcing these projects I dont care about how many projects someone has, just if the busy phases stack up onto each other.

So I want a gantt chart that shows 1 line per project with each phase a different colour. I also want to be able to filter the chart by projects on 4 other columns in the dataset.

how do i?

So I want a gantt chart that shows 1 line per project with each phase a different colour

This sounds like conditional formatting

what with 1 cell per week, thought of that; so messy, so clumsy, so not mega awesome....

maybe I should because current efforts at a pivot chart on its side seems to be pretty similar to me just smashing stuff up with a hammer.

can you format using an if function?

Yes.

Just make sure that the cell references in the formula match up with the cell references in the range.

And be aware that inserting / copying rows & columns can cause there to be a shit tonne of overlapping conditional format ranges.

=IF(AND(M$1>=$F2,M$1<=$G2),1,(IF(AND(M$1>=$G2,M$1<=$H2),2,(IF(AND(M$1>=$H2,M$1<=$I2),3,(IF(AND(M$1>=$I2,M$1<=$J2),4,(IF(AND(M$1>=$J2,M$1<=$K2),5,(IF(AND(M$1>=$K2,M$1<=$L2),6,0)))))))))))

58008

This may be useful

https://www.smartsheet.com/blog/gantt-chart-excel

I did something like this for my parental leave. You want to haz?

Dam auto rotate on my phone

Please, do you need my email?

Yeah...

I do have something along the line as well I use to schedule my workload, if interested

anyone got an idea on how I can automate the following:

Each row is a contractor it records their start and end date for the contract and has a unique ID for - Contractor, Job, and Placement.

There may be multiple rows for each contractor, some of these multiples will be for the same job as they have had their contracts extended. Some of the multiples will be because they have worked on more than one different jobs.

I need to de dupe these records so that I have one row per contractor per job where if the contract has been extended it takes the earliest date from the start date and the latest date from the end date.

Any ideas or is the guy in finance stuck with doing a boring manual review of these records each week?

Personally, I'd append a couple of things together (Contractor, job and placement I'd guess) to create a unique reference, do a pivot table with that unique reference and whatever else you want and use max/min on the dates to pick up the earliest start date and latest end date.

Or, do similar but have a look at this https://www.extendoffice.com/documents/excel/2683-excel-find-max-min-value-based-on-criteria.html for picking out start/end dates

Can't use a PT unfortunately.

Thinking aloud- If I used a MAX/MIN based formula to create two new columns with dates I want (would I use an HLOOKUP or similar to do this?) and then had a third column with the number of days between the two, sort largest to smallest and then de dupe based on unique combined job+contractor ID would that theoretically work?

Either that or:

Use max/min with criteria (from the link in my previous post) to find new earliest/latest dates. Paste values on those earliest/latest dates (or paste them over the original dates in the data depending how you want it to look/be used afterwards). Use DATA->REMOVE DUPLICATES on the unique identifier to remove the extra lines.

Or (and this is probably what I'd do unless there was loads of data so I could preserve the original data). Use max/min with criteria (from the link in my previous post) to find new earliest/latest dates. Create a separate sheet with the unique identifier going down the leftmost column, use vlookups to pull the data from the data sheet pulling out the corrected dates.

I think index match might be your friend here, based on the unique identifier.

I always prefer (and use) index/match instead of vlookup- no need to sort columns and the calculation process is suposed to be less resource intensive.

@aggi cheers- I can feel some sort of hybrid of the above coming together.

As always this forum rocks! (though Chandoo.org is pretty good for vba help).

You could use custom sorting to sort by contractor and then by date, so that all the rows that concur to the same contractor are such that the first and last are of interest, regardless of the amount of data per contractor.
Then write a formula to pull these out such that if the contractor of a given row is different from the above/below you get the unique index, otherwise blank.
You are then left with a column of blanks and ID of the entries you need, just filter out the blanks and you have all you need.

No?

Followed that up until the conditional formula.

Assuming your table is

column A: unique ID
column B: Contractor
column C: Date begin
column D: Date end

and starts from row 2

=IF(B2<>B1,A2,IF(B2<>B3,A2,""))

Shoud work

Does it make more sense now?