Multiple If formula

[PHIL.Pearce84]

HI,

I am trying to get a multiple if statement to work but I've lost myself in the confusion, can anyone help?

Basically this is what I want to achieve;

1. If the completion date in Column J falls within any of the date ranges on the sheet (start date in D END DATE in I)
2. And if the values in G3,f3,e3 and C3 (which will continue up to row 2000 G4,5 ETC) then return yes otherwise no


This is my formula;

=IF(AND(J:J>=D3,I3<=I:I,"YES"),IF(G:G=G3,"YES"),IF(F:F=F3,"YES"),IF(E:E=E3,"YES"),IF(C:C=C3,"YES"),"No"))

 

[Joe4]

You can compare a whole column worth of values to a single cell like the way you have written, i.e.

[COLOR=#333333]=IF(AND(J:J>=D3,...[/COLOR]

What is it exactly that you are trying to do?
Are you really trying to compare every single entry in column J to the value in D3 at once and return something to a single cell?
If you are trying to compare one cell at a time, then use a single cell reference, i.e.

[COLOR=#333333]=IF(AND(J1>=$D$3,...[/COLOR]

 

[PHIL.Pearce84]

I am trying to determine where repair jobs have been raised separately but should in reality be consolidated into one.

Each line of the work sheet contains a separate job, I need to determine if.

1. Job type matches another
2. The estimated completion date falls within the same date range as another (so could be completed at the same time)
3. The status i.e complete or scheduled is the same as another
4. The address matches another job

If the answer to each of these is yes then I know the jobs should be combined.

 

[Joe4]

I think it would be beneficial if we could see a sampling of your data and expected results.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[PHIL.Pearce84]

Thank you, does this help? I've labeled the columns

A	C	D	E	F	G	I	J
Repairs Request Ref	ADDRESS_LINE_1	DATE_LOGGED	STD_JOB_DESCRIPTION	PRIORITY	STATUS	Complete by?	COMPLETION_DATE
4347739	1- 18, COCHRANE HOUSE 19, TRUESDALES	13/06/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	04/07/2017	19/06/2017
4304911	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	18/05/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	08/06/2017	31/05/2017
4266187	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	25/04/2017	Door (external) - replace	60 Days	Job Scheduled	18/07/2017	(blank)
4338217	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	07/06/2017	Door Entry Electric System- repair/ replace	Reactive - Average 15 working days	Job Complete	28/06/2017	20/06/2017
4262416	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	24/04/2017	Door External (timber) - repair	24 Hour Emergency	Job Complete	25/04/2017	24/04/2017
4188018	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	13/03/2017	Door External (UPVC) - repair	Reactive - Average 15 working days	Job Complete	03/04/2017	16/03/2017
3967380	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	10/11/2016	Drain Blockage - clear as per agreed rate	Reactive - Average 15 working days	Job Complete	01/12/2016	08/12/2016
4052289	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	31/12/2016	Drain Blockage - clear as per agreed rate	24 Hour Emergency	Job Complete	01/01/2017	04/04/2017
4143731	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	17/02/2017	Drain Blockage - clear as per agreed rate	24 Hour Emergency	Job Complete	18/02/2017	18/02/2017
4213071	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	27/03/2017	Lights	Reactive - Average 15 working days	Job Complete	17/04/2017	28/03/2017
4392122	1-11 (BLOCK B4) WYATT COURT FARLEY FIELDS	08/07/2017	Out of Hours - Electrics	24 Hour Emergency	Job Complete	09/07/2017	08/07/2017
4194263	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	15/03/2017	Communal Lift - repair	24 Hour Emergency	Job Complete	16/03/2017	22/03/2017
4194271	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	15/03/2017	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete	05/04/2017	28/03/2017
4307155	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	19/05/2017	Gate - repair/ refix inc latches	Reactive - Average 15 working days	Job Complete	09/06/2017	14/06/2017
4307113	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	19/05/2017	Skirting/ Architrave - repair	Reactive - Average 15 working days	Job Complete	09/06/2017	06/06/2017
4307121	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	19/05/2017	Window (single glazed) - measure	Reactive - Average 15 working days	Job Complete	09/06/2017	02/06/2017
4302006	1-20 (BLOCK C1) BAGSHAWE COURT FARLEY FIELDS	17/05/2017	Work from service visit	Reactive - Average 15 working days	Job Complete	07/06/2017	24/05/2017
4362050	12-19 (BLOCK B3) WYATT COURT FARLEY FIELDS	21/06/2017	Communal Lighting - repair multiple lights	24 Hour Emergency	Job Complete	22/06/2017	21/06/2017
4306868	12-19 (BLOCK B3) WYATT COURT FARLEY FIELDS	19/05/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	09/06/2017	31/05/2017
4306884	12-19 (BLOCK B3) WYATT COURT FARLEY FIELDS	19/05/2017	Door Closer - repair	Reactive - Average 15 working days	Job Complete	09/06/2017	06/06/2017
4223228	12-19 (BLOCK B3) WYATT COURT FARLEY FIELDS	31/03/2017	Door Entry Electric System- repair/ replace	Reactive - Average 15 working days	Job Complete	21/04/2017	10/04/2017
4306876	12-19 (BLOCK B3) WYATT COURT FARLEY FIELDS	19/05/2017	Door Entry Electric System- repair/ replace	Reactive - Average 15 working days	Job Complete	09/06/2017	19/06/2017

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

 

[Joe4]

OK, now based on that data sample, can you walk us through one of the records, and explain exactly what should happen to that record and why?
Where I am getting confused from your earlier posts is that I cannot tell what should be contained to the single line/record you are looking at versus the whole set of data. So if you can walk us through an actual example, it may become clearer to us how you want this to work.

 

[PHIL.Pearce84]

Thank you for you patience, I'm aware that I'm not really being very clear! I've provided a smaller sample which contains a record I would expect to flag up.

Ok, I am trying to identify where I can make efficiency savings by sending one contractor out instead of 2/3 by identifying where the same job is currently booked to be attended to in the future.

For the records highlight red I would want to return a "yes" because:

1. The address Matches
2. As you can see the job scheduled to be completed on the 26/4/17 could in theory be done during the visit on the 25/4/17. I want to highlight that there is already a job of the same type (column E) so that they can be grouped together. I could add in whether a job is scheduled or complete if it would help? As obviously if the previous job has been completed already then I can't combine them.
3. Priority status is the same, i.e. both "Reactive - Average 15 working days"
4. It's for the same address
5. It's the same job type "Communal Lighting - repair single light"

The other records are not repeated and don't meet the above criteria so should not be flagged.

Does that help in anyway?

A	C	D	E	F	G	I	J
Repairs Request Ref	ADDRESS_LINE_1	DATE_LOGGED	STD_JOB_DESCRIPTION	PRIORITY	STATUS	Complete by?	COMPLETION_DATE
4241759	BLOCK 1-18 TANTON HOUSE WINSLOW FIELD	12/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	03/05/2017	25/04/2017
4268646	BLOCK 1-18 TANTON HOUSE WINSLOW FIELD	26/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	17/05/2017	26/04/2017
4238285	BLOCK 12-22 GREAT BEANHILLS	10/04/2017	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete	01/05/2017	04/05/2017
4348159	BLOCK 1-23, OSBORN HOUSE HOWARDSGATE	13/06/2017	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete	04/07/2017	19/06/2017
4268373	BLOCK 1-24 ATLAS HOUSE. BELLINGDON ROAD	26/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	17/05/2017	02/05/2017
4240272	BLOCK 1-29 GOWER HOUSE THE GOWERS	11/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete	02/05/2017	24/04/2017
4241840	BLOCK 129-141 (ODD) WRIGHTS LANE	12/04/2017	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete	03/05/2017	12/04/2017

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

 

[Joe4]

I think that is going to be a bit more complex than just a regular formula, because it is not enough that there are other entries in the same column that match the entry, but all those row numbers need to match too.

For example, if you look at Job Description, it is not enough that "Communal Lighting - repair single light" is found multiple times in that column, it has to appear multiple in conjunction with the other field (like Address) being the same also.

I think something like that is either going to take some complex array formula, or VBA. If it were me, I would probably write VBA code to sort through the data and then loop through all the records.

 

[Eric W]

Maybe a COUNTIFS could work?

	A	B	C	D	E	F	G	H	I	J	K
1	Repairs Request Ref		ADDRESS_LINE_1	DATE_LOGGED	STD_JOB_DESCRIPTION	PRIORITY	STATUS		Complete by?	COMPLETION_DATE
2	4241759		BLOCK 1-18 TANTON HOUSE WINSLOW FIELD	######	Communal Lighting - repair single light	Reactive - Average 15 working days	Scheduled		3/5/2017	25/04/2017	Can bundle
3	4268646		BLOCK 1-18 TANTON HOUSE WINSLOW FIELD	26/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Scheduled		17/05/2017	26/04/2017	Can bundle
4	4238285		BLOCK 12-22 GREAT BEANHILLS	######	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete		1/5/2017	4/5/2017
5	4348159		BLOCK 1-23, OSBORN HOUSE HOWARDSGATE	13/06/2017	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete		4/7/2017	19/06/2017
6	4268373		BLOCK 1-24 ATLAS HOUSE. BELLINGDON ROAD	26/04/2017	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete		17/05/2017	2/5/2017
7	4240272		BLOCK 1-29 GOWER HOUSE THE GOWERS	######	Communal Lighting - repair single light	Reactive - Average 15 working days	Job Complete		2/5/2017	24/04/2017
8	4241840		BLOCK 129-141 (ODD) WRIGHTS LANE	######	Communal Lighting - repair multiple lights	Reactive - Average 15 working days	Job Complete		3/5/2017	######

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula K2 =IF(COUNTIFS(C:C,C2,E:E,E2,F:F,F2,G:G,"*schedule*")>1,"Can bundle","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This checks for jobs that match address, description, priority, and are still scheduled. If we have multiple matches, you'll get the message. If the jobs are not adjacent, it might be tougher to identify the matches though.

 

[PHIL.Pearce84]

HI Thanks for this, don't suppose you have any VBA that might work?