COUNTIF would solve this? Tricky one.

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
G'day,

Another tricky one in my mind.

I have 100 jobs multiple lines per job with the same JOBID found in column A starting in A2.

I also have a description of material in Column W. Starting at W2.

What I need to do is if the BLOCK is the same job ID (column a) and "Ridge300" is found in Column W then AC column needs to be "1" against each row which matches this criteria.

Looking something like this: Thanks for your help!

JOB IDMachineFLAG
1xxx1
1Ridge3001
1xxx1
1xxx1
1xxx1
25Ridge3001
25xxx1
25xxx1
3Ridge400
3xxx
3xxx
3xxx
3xxx
5xxx1
5xxx1
5xxx1
5Ridge3001
5xxx1
5xxx1

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,

If you have COUNTIFS:


Book1
AWAC
1JOB IDMachineFLAG
21xxx1
31Ridge3001
41xxx1
51xxx1
61xxx1
725Ridge3001
825xxx1
925xxx1
103Ridge400
113xxx
123xxx
133xxx
143xxx
155xxx1
165xxx1
175xxx1
185Ridge3001
195xxx1
205xxx1
Sheet225
Cell Formulas
RangeFormula
AC2=IF(COUNTIFS(A$2:A$20,A2,W$2:W$20,"Ridge300"),1,"")


Adjust cell references/range as needed, formula copied down.
 
Upvote 0
Hi,

If you have COUNTIFS:

AWAC
1JOB IDMachineFLAG
21xxx1
31Ridge3001
41xxx1
51xxx1
61xxx1
725Ridge3001
825xxx1
925xxx1
103Ridge400
113xxx
123xxx
133xxx
143xxx
155xxx1
165xxx1
175xxx1
185Ridge3001
195xxx1
205xxx1

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

Worksheet Formulas
CellFormula
AC2=IF(COUNTIFS(A$2:A$20,A2,W$2:W$20,"Ridge300"),1,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Adjust cell references/range as needed, formula copied down.

You sir - are amazing thank you so much exactly what I needed ! thank you thank you!:eek:
 
Upvote 0
You're quite welcome, glad to help, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top