Main Area and Open Discussion > General Software Discussion
Working with excel row and columns
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
Go to full version