ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

Main Area and Open Discussion > General Software Discussion

Working with excel row and columns

(1/2) > >>

SpoilerPosted 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:

This is what I need:

Any ideas on how I can do this?

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


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

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

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)

--- ---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)

Working with excel row and columns

credit for the code goes here -

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.


[0] Message Index

[#] Next page

Go to full version