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

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