need help organizing spreadsheet
March 29, 2010 5:23 AM   Subscribe

I need to organize a spreadsheet of log files into specific columns. The spreadsheet is a converted text column, each log file parses things differently, so column 'a' on one row can be userid, while column 'd' on row is the userid. I want to organize this spreadsheet, so that column a is userid for all logs. is there a specific excel formula or macro i should use, i was thinking: if cell contains userid, move to column 'x'. just not sure how to do this. - Tim
posted by fozzie33 to Computers & Internet (5 answers total)
 
Is there anything unique about the userid?

Is it always, for example, 7 characters long or always has a certain prefix?

If it were always 7 characters long you could use this function in a spare column.
Pretend this is input in cell e1 and assuming all the userids have 7 characters, but nothing else in your data has only 7 characters.
=IF(LEN(A1)=7,A1,IF(LEN(B1)=7,B1,IF(LEN(C1)=7,C1,IF(LEN(D1)=7,D1))))

Same with a prefix, I've used U as an example for a prefix again in cell e1.
=IF(LEFT(A1,1)="U",A1,IF(LEFT(B1,1)="U",B1,IF(LEFT(C1)="U",C1,IF(LEFT(D1)="U",D1))))
You could combine those 2 functions (or others) if the userid always had a prefix and also always was a certain length.
posted by selton at 6:12 AM on March 29, 2010


its not just userid, there are other fields, just all in different columns per row...
so there are ip addresses, date of birth, userid, application name, etc...

i tried lookup function, but it isn't working on all rows.
- Tim
posted by fozzie33 at 6:20 AM on March 29, 2010


OK I understand.

Are there random empty cells dotted within the rows or are the empty ones always at the start like this ?


row 1. userid dob ip
row 2. empty userid dob ip
row 3. empty empty userid dob ip
row 4. userid dob ip


If it's like that I'd highlight all the data and do a sort on the first column and delete the empty cells as they come to the top/bottom of the range (delete>shift cells left).
posted by selton at 6:58 AM on March 29, 2010


it'd be more something like this

userid dob ip sessionid
otherid sessionid dob ip
ip sessionid otherid otherstuff dob

does this help?
posted by fozzie33 at 7:29 AM on March 29, 2010


Had a little play about, I'm not VB proficient, but this seems to work alright. It inserts a new column at the front, then looks through the first 100 rows & 10 columns (you can change that easily in the code), if it finds a match it puts the match in the front column. Probably should use a While loop instead and make stop looking when the row/column is empty-- but depends if you have blank data in your table.

The match it's doing is just looking for data starting with a "U" you can change that in the If Left(Cells(down, across), 1) = "U" Then bit.

To use (I'm sure there's an easier way, but again, excel novice here) in Excel press Alt+F11 then click Insert Module and paste the below into it. Save and quit the editor, then press Alt+F8 and click ReJig and press Run.
Sub ReJig()

    ' Insert a blank Column at the start
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    
    ' Iterate through the cells
    For down = 1 To 100
        For across = 1 To 10
            ' If we find a cell starting with U, then
            ' put that cells value in the next column
            If Left(Cells(down, across), 1) = "U" Then
                Cells(down, 1) = Cells(down, across)
            End If
        Next across
    Next down
End Sub

posted by Static Vagabond at 9:05 AM on March 29, 2010


« Older Mystery sticker on candy packaging   |   Does anyone know when the Australian Net-Filter... Newer »
This thread is closed to new comments.