Using Excel to analyse server logs

[mikekirk]

Hi,

I need to use Excel to analyse some server logs, but I'm having trouble getting the data into the format I need. The current data is in a text file (no problem importing that), and it looks something like this

UniqueID Data
8598A XYZ
8598A IUI
8599B WOR
8599B JKM
8599B LOL

I want to transpose it to look like this.

UniqueID Data1 Data 2 Data3 etc.
8598A XYZ IUI
8599B WOR JKM LOL

One of the biggest problems is that each UniqueID may have different numbers of lines associated with it.

Can anyone help?

 

[Derek Brown]

Hello and welcome to The Board.
If it is a manual process that you want:
First split the data into 2 columns, with the headings in row 1.
in C2 enter the formula:
=IF(A2=A1,IF(A2<>A3,2,1),0)
and copy it down to the other rows.
In D2 enter the formula:
=IF(C2=0,A2&"!"&B2,D1&"!"&B2)
and copy it down to the other rows.
You now need to convert columns C and D from formulas to values;
sort on column C descending;
remove the records with 1 or 0 in column C
and then convert column D using text to columns with the delimiter "!".
I hope that I have described that correctly but let me know if it does not work.

 

[mikekirk]

Genius! Thanks very much, that worked a treat.