Excel formula problem Closed

Question

Hi there,  
 Was hoping some one out there could help me with an excel formula problem... I have a Table ----
A                 B            C           D             E
Date         Account  Equip    Fault ref   Fault type 
21/02/10    301      SEG       43216      U/S 
21/02/10    304      Tower   43687      Switching issue 
23/02/10    313      Surface 44678      Uneven finish 
24/02/10    321      Digital   45639      U/S 
28/02/10    302      AGL       46873      Non operational 
03/03/10    304      Tower   47821      Switching issue 
08/03/10    309      Screen  48098      Lighting U/S 
14/03/10    304      Tower   56473      Switching issue 
15/03/10    323      Guard    57128     Lighting U/S 
20/02/10    341       FEG      64234       No power supply 

From this table I need to find the most recent fault details for a particular account eg: 304 and display the adjacent cells information on a different page.... 
 Please help, I'm ok with most of the basic excel formulas, but I'm still learning, I know how to find the most recent date, and how to display adjacent cell information, but cant work out how to combine the two... to be honest I'm not really sure if this would be the best solution... 
Any help anyone could give me would be most gratefully received!!!

rizvisa1 · Answer

How do you intend to find ? I mean how would you search for account number ? Would there be a cell where you would type in this number or you would enter that in an inputbox or is there some other way ?

Brujah · Answer

HI, there would be a cells on a different page that would list all the account numbers with the most recent fault details adjacent , does that help any? (The intention is that the description of the fault on each asset/account would be displayed against that assets/account location on a excel based map which i've already got)

Brujah · Answer

Forgot to say, the information on the original entry page, with the table, is a permenant record, and has a fault closure column as well, although not essential it would be good if there was a way to get the live map display of the most recent fault details to go blank if the fault has been resolved... but i think i may just be confusing the issue now... again tho, thanx in advance for any help you can give me!!

rizvisa1 · Answer

Could you please post a sample workbook at some share site like https://authentification.site and post a link here.

Brujah · Answer

Hi,

Please find the link to my trial fault rep attached, I couldn't get it to upload fully with map, so you're gonna have to use imagination a bit... bit i need is the most recent fault detail for the asset type to appear adjacent to the appropriate coloured assest box on the area 1 .... i've typed examples next to them to give you an idea of what i mean.. I just cant seem to get a formula for both finding the most recent entry for the asset, and get the detail to transfer across... can seem to do either or but never together!!!

Been using this  as a start off point for finding the date {=MAX(IF(Faults!A2:A18=34244,Faults!B2:B18,))} but can't get it to let me bring up the rest of the detail...

https://authentification.site/files/21628387/Trial_fault_rep.xls

Thanx again for looking at this!!!

rizvisa1 · Answer

is it possible to have a new column on fault as concatenation of all other columns that had data

just a step further from your formula 
INDIRECT(ADDRESS(MATCH(MAX(IF(Faults!A1:A18=AI24,Faults!B1:B18)),Faults!B1:B18,0), 2,,,"Faults"))

Brujah · Answer

Hi again, thank you for the above, I dont know what i'm doing wrong this time, but what on your formula do i need to change for it to pick up on the conatenate column (now available in column H1:H18) every time i try i either get an error or N/A response... sorry to be a pain....

rizvisa1 · Answer

It has been some time. It just slipped my mind till I saw the excel file today

This is based on the sample file that you provided

In your sheet Faults in column G add all the columns that you want to see as a result of formula
Copy this on cell G2 and copy down

=TEXT(B2,"mm/dd/yyyy")&"   "&C2&"   "&D2&"   "&E2


Now on your Area Sheet use this Array formula ( this is looking up the value shown in cell AI24

 Again this is array formula so you need to use CTRL + SHIFT + ENTER 

=INDIRECT(ADDRESS(MAX(IF((Faults!B$1:B$700=MAX(IF(Faults!A$1:A$700=AI24,Faults!B$1:B$700,0))) * (Faults!A$1:A$700=AI24)=1,ROW(Faults!A$1:A$700),0)),7,1,TRUE, "Faults"))


There is one more way of using this formula. Have all the Assets and look up on a separate sheet (see sheet 5 in attached book) 

then you can use it as
=IF(OR(ISERROR(Sheet5!A9),Sheet5!A9=0),"",Sheet5!B9)


https://authentification.site/files/22377789/Trial_fault_rep.xls

Excel formula problem

8 responses

Similar discussions

Subscribe To Our Newsletter!