Working with excel row and columns

[rgdot]

Spoiler (click to show/hide)

Posted this on stackoverflow but my initial question was confusingly worded and by the time I clarified I guess the question got lost in the maze there

I am trying to work with an Excel file (actually using Libreoffice) that is an export of a website content database. Since I am not even sure how to explain the problem I made a small version of what I am trying to do. The real file has many more records of course.

This is what I have:

[attachimg=#]

This is what I need:

[attachimg=#]

Any ideas on how I can do this?

[Hardcopy]

Take a look at pivot tables in excel. They will give you what you need.  
Open office has something similar. 

Hardcopy

[kunkel321]

Also checkout a free Excel addin called ASAP-Utilities.  It lets you transpose like that.  

EDIT:  Yea, I guess ASAP won't do that. 

[Target]

it's not exactly transposing (that was my first thought)

and the transpose formula is built in to both excel and libreoffice (no need for an addin)

FWIW here's a bit of VBA I found on the web that does what you want, though the output is to a delimited string (pretty sure you can work it out from there)

Code

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
    If r = lookupval Then
        result = result & "," & r.Offset(0, indexcol - 1)
    End If
Next r
MYVLOOKUP = Right(result, Len(result) - 1)
End Function

paste it into your (excel) personal macro workbook, then call it as a user defined function from the FX dialog box (syntax is the same as a normal VLOOKUP)



credit for the code goes here - http://www.mrexcel.com/forum/excel-questions/280705-vlookup-return-multiple-values-one-cell-concatenate.html

[IainB]

I don't know how you would be able to do that in a LibreOffice spreadsheet, but I would hazard a guess that a LibreOffice forum/discussion group might be able to help. This sort of requirement probably won't be a "new" one.

[AndyM]

The real file has many more records of course.

How many records (rows)?

Is this a one-time task?

[AndyM]

?

[skwire]

?

I banned the spammer (and his/her posts).

[rgdot]

Thanks for the help/suggestions, will try/test them.

Pivot tables may be doable but I am not an expert. Not exactly a transpose.

@IainB I should post there, that's true. Not sure what to search for though, I mean what would be the search query, so yes post it is.
@AndyM. One time yes. ~40k rows.

[TaoPhoenix]

Also checkout a free Excel addin called ASAP-Utilities.  It lets you transpose like that. 

I've used ASAP-Utilities and I was impressed, even though I didn't need 95% of what it did.