Main Area and Open Discussion > General Software Discussion

Working with excel row and columns

(1/2) > >>

rgdot:
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?

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)


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

Navigation

[0] Message Index

[#] Next page