Excel formula problem

Closed
Report
Posts
5
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 27, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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!!!

8 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 ?
0
Posts
5
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 27, 2010

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)
0
Posts
5
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 27, 2010

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!!
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please post a sample workbook at some share site like https://authentification.site and post a link here.
0
Posts
5
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 27, 2010

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!!!
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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"))
0
Posts
5
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 27, 2010

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....
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0