Formula/Table To Make Data Readable

[karlm14]

Hi,

New to the forum looking for some excel help.

I have a table of raw data which in different columns shows; serial number, last seen on network and last location on network amongst other things. How do I build a table that gives me an easy to read status for each device of when it was very first seen on the network and at what locations and then the next time it was seen and at what location etc etc.

Something like the following or anything that is more readable than line by line;

001
01/01/2021 - Location A
10/01/2021 - Location B
002
01/01/2021 - Location D
20/01/2021 - Location A

Data currently looks like

serial	last seen on network	last location
001	01/01/2021	A
002	01/01/2021	D
002	20/01/2021	A

Hope this makes sense, sorry for not being as clear as I would like.

Thanks

K

 

[JamesCanale]

Best I could do. I think a macro would make it just the way you like though.

MrExcelPlayground4.xlsx
	A	B	C	D	E	F
1	serial	last seen on network	last location		Serial	Date - Location
2	001	1/1/2021	A		001	1/1/2021 - A
3	002	1/1/2021	D		001	1/10/2021 - B
4	002	1/20/2021	A		001	1/11/2021 - D
5	001	1/10/2021	B		001	1/13/2021 - B
6	001	1/11/2021	D		002	1/1/2021 - D
7	003	1/12/2021	C		002	1/20/2021 - A
8	001	1/13/2021	B		003	1/12/2021 - C
9	003	1/14/2021	D		003	1/14/2021 - D
10	003	1/15/2021	C		003	1/15/2021 - C
Sheet37

Cell Formulas
Range	Formula
E2:E10	E2	=INDEX(SORT(A2:C10,{1,2},1),,1)
F2:F10	F2	=TEXT(INDEX(SORT(A2:C10,{1,2},1),,2),"m/d/yyy")&" - "&INDEX(SORT(A2:C10,{1,2},1),,3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:F10	Expression	=ISODD(COUNTA(UNIQUE($E$2:$E2)))	text	NO