Formula needs tweeked

N

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
223
Office Version
  1. 2016
=IF(N327356="A",0,IFNA(AB327356-LOOKUP(2,1/(N$2:N327356="A")/(B$2:B327356=B327356),AB$2:AB327356),""))

In this formula I'm asking it to search for "A" and how long it has been since "A" was done based off the same identifier in column B.

What I'm wondering is, can this code be modified to look for "A", "B", "C", "E", and "I" at the same time? So instead of possibly 200 days lapsed just for "A" it will show 45 days lapsed based off this new information it is also looking for.

Column B = Numeral Number Assigned to a Truck
Column N = Work Code. ie: A, B, C, D, E, I
Column AB = Date Work Done
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Kindly give one sample data along with answer that you require
 
Upvote 0
earthworm said:
Kindly give one sample data along with answer that you require
Click to expand...
Maintenance Cost Database Dive.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
89961TRAC16820VENDROROAD REPAIRMAMAINT01HVAC01ACHOOD002COMPRS64MOTEL99NA12BROKEN0.00$0.00$0.00$0.0019 Jul 20352,812352,81216700--168997/25/2020353,355,143.00R1164546012018INTL8/15/2017C1LTCOMPLAINT: NIGHT 1143
89962TRAC16823VENDROROAD REPAIROPOPER00PWRUNT00TRAC001UNIT64MOTEL99NA00NOFAIL0.00$0.00$0.00$0.0019 Jul 20288,437288,43716700--168997/25/2020353,355,150.00R1164550012018INTL8/15/2017C1LTCOMPLAINT:HOTEL HURRICANE 194
89963TRAC16842TERMMUMURRAYMAMAINT57APU-EL01HVAC009BLWMTR03RPLNEW99NA38SHORT0.50$27.50$262.02$289.5219 Jul 20346,534346,54116700--168997/25/2020353,355,127.00S1155071012018INTL8/28/2017C1LTBUNK AC INOP123
89964TRAC16847VENDROROAD REPAIRDEDRVERR42COOLSY05CONTRL001SENSOR09DIANOS99NA00NOFAIL0.00$0.00$0.00$47.3719 Jul 20276,502285,81716700--168997/25/2020353,355,167.00R1164560012018INTL8/28/2017C1LTCOMPLAINT:COOLANT LEAK C A43
89965TRAC16855TERMWMWEST MEMPHISPMPM00PWRUNT00TRAC001UNITBB INSP99NA01DUE0.20$11.00$0.00$11.0019 Jul 20288,585289,26316700--168997/25/2020343,355,219.00S1157839012018INTL10/10/2017C1LTCSA153
89966TRAC16893TERMMUMURRAYMAMAINT01HVAC04ACBUNK020BKBLOW03RPLNEW99NA38SHORT0.70$38.50$262.02$300.5219 Jul 20344,458346,02216700--168997/25/202033NLMURPTL-NORTH LOT3,354,516.00S0115562012018INTL10/17/2017C1LTBUNK AC DONT BLOW AIR17
89967TRAC16893TERMMUMURRAYMAMAINT44FUELSY11SPDCTL016CRUSWT09DIANOS99NA00NOFAIL0.50$27.50$0.00$27.5019 Jul 20344,458346,02216700--168997/25/202033NLMURPTL-NORTH LOT3,354,516.00S0115562012018INTL10/17/2017C1LTCRUISE CONTROL WORKS SOMETIMES17
Sheet1


The far right is where the formula "was". The file is so large that once the formula is done calculating, I copy and paste values so it doesn't lag up the computer.
 
Upvote 0
Noxqss38242 said:
Maintenance Cost Database Dive.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
89961TRAC16820VENDROROAD REPAIRMAMAINT01HVAC01ACHOOD002COMPRS64MOTEL99NA12BROKEN0.00$0.00$0.00$0.0019 Jul 20352,812352,81216700--168997/25/2020353,355,143.00R1164546012018INTL8/15/2017C1LTCOMPLAINT: NIGHT 1143
89962TRAC16823VENDROROAD REPAIROPOPER00PWRUNT00TRAC001UNIT64MOTEL99NA00NOFAIL0.00$0.00$0.00$0.0019 Jul 20288,437288,43716700--168997/25/2020353,355,150.00R1164550012018INTL8/15/2017C1LTCOMPLAINT:HOTEL HURRICANE 194
89963TRAC16842TERMMUMURRAYMAMAINT57APU-EL01HVAC009BLWMTR03RPLNEW99NA38SHORT0.50$27.50$262.02$289.5219 Jul 20346,534346,54116700--168997/25/2020353,355,127.00S1155071012018INTL8/28/2017C1LTBUNK AC INOP123
89964TRAC16847VENDROROAD REPAIRDEDRVERR42COOLSY05CONTRL001SENSOR09DIANOS99NA00NOFAIL0.00$0.00$0.00$47.3719 Jul 20276,502285,81716700--168997/25/2020353,355,167.00R1164560012018INTL8/28/2017C1LTCOMPLAINT:COOLANT LEAK C A43
89965TRAC16855TERMWMWEST MEMPHISPMPM00PWRUNT00TRAC001UNITBB INSP99NA01DUE0.20$11.00$0.00$11.0019 Jul 20288,585289,26316700--168997/25/2020343,355,219.00S1157839012018INTL10/10/2017C1LTCSA153
89966TRAC16893TERMMUMURRAYMAMAINT01HVAC04ACBUNK020BKBLOW03RPLNEW99NA38SHORT0.70$38.50$262.02$300.5219 Jul 20344,458346,02216700--168997/25/202033NLMURPTL-NORTH LOT3,354,516.00S0115562012018INTL10/17/2017C1LTBUNK AC DONT BLOW AIR17
89967TRAC16893TERMMUMURRAYMAMAINT44FUELSY11SPDCTL016CRUSWT09DIANOS99NA00NOFAIL0.50$27.50$0.00$27.5019 Jul 20344,458346,02216700--168997/25/202033NLMURPTL-NORTH LOT3,354,516.00S0115562012018INTL10/17/2017C1LTCRUISE CONTROL WORKS SOMETIMES17
Sheet1


The far right is where the formula "was". The file is so large that once the formula is done calculating, I copy and paste values so it doesn't lag up the computer.
Click to expand...

Column "N" contains work codes. Typically numbers but the "A","B","C","D","E","I" work codes are types of inspections. I am wanting to calculate how many days since the last time the unit was inspected to the present work line. The code earlier only looks up how many days elapsed since last "A" inspection. Can I incorporate more than 1 value it is looking for?
 
Upvote 0
Noxqss38242 said:
=IF(N327356="A",0,IFNA(AB327356-LOOKUP(2,1/(N$2:N327356="A")/(B$2:B327356=B327356),AB$2:AB327356),""))

In this formula I'm asking it to search for "A" and how long it has been since "A" was done based off the same identifier in column B.

What I'm wondering is, can this code be modified to look for "A", "B", "C", "E", and "I" at the same time? So instead of possibly 200 days lapsed just for "A" it will show 45 days lapsed based off this new information it is also looking for.

Column B = Numeral Number Assigned to a Truck
Column N = Work Code. ie: A, B, C, D, E, I
Column AB = Date Work Done
Click to expand...

Will an IF(OR( Formula work?
 
Upvote 0
Try changing

(N$2:N327356="A")

to

ISNUMBER(SEARCH(N$2:N327356,"ABCEI"))

in the formula.
 
Upvote 0
Like this?
=IF(N163643="B",0,IFNA(AB163643-LOOKUP(2,1/(ISNUMBER(SEARCH(N$2:N163643,"ABCEI")/(B$2:B163643=B163643),AB$2:AB163643),""))))

If so, it said I entered too many arguments for this function...
 
Upvote 0
Darn pesky parentheses! Try:

Excel Formula: 
=IF(N163643="B",0,IFNA(AB163643-LOOKUP(2,1/ISNUMBER(SEARCH(N$2:N163643,"ABCEI"))/(B$2:B163643=B163643),AB$2:AB163643),""))
 
Upvote 0
Solution
Eric W said:
Darn pesky parentheses! Try:

Excel Formula: 
=IF(N163643="B",0,IFNA(AB163643-LOOKUP(2,1/ISNUMBER(SEARCH(N$2:N163643,"ABCEI"))/(B$2:B163643=B163643),AB$2:AB163643),""))
Click to expand...
That worked brilliantly...so brilliant that I ran into another issue.

Is there a separate formula to then pull the information on the previous ABCEI containing row column AF data?

So, it looks for last "ABCEI" entered and pulls data from cell AF? Essentially telling me who did the inspection is now the goal.
 
Upvote 0
Maybe:

Excel Formula: 
=IF(N163643="B",0,IFNA(LOOKUP(2,1/ISNUMBER(SEARCH(N$2:N163643,"ABCEI"))/(B$2:B163643=B163643),AF$2:AF163643),""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
157
awoohaw
awoohaw
J
  • Question
Formula to move information to calendar
Replies
0
Views
82
jc352
J
J
  • Solved
IFS
Replies
4
Views
100
Peter_SSs
Peter_SSs
J
  • Question
Formula to find date and return column number
Replies
2
Views
159
jc352
J
D
  • Question
I'm looking for help to find a formula.
Replies
4
Views
96
devinberry1997
D

Forum statistics

Threads
1,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

Share this page

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