Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 07, 2016, 09:56:23 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Working with excel row and columns  (Read 2689 times)

rgdot

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 1,880
    • View Profile
    • Donate to Member
Working with excel row and columns
« on: April 07, 2014, 12:21:29 PM »
Spoiler
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:

Screenshot-1.png

This is what I need:

Screenshot-3.png

Any ideas on how I can do this?



Hardcopy

  • Participant
  • Joined in 2014
  • *
  • default avatar
  • Posts: 1
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #1 on: April 07, 2014, 02:39:22 PM »
Take a look at pivot tables in excel. They will give you what you need.  
Open office has something similar. 

Hardcopy

kunkel321

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 464
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #2 on: April 07, 2014, 08:31:31 PM »
Also checkout a free Excel addin called ASAP-Utilities.  It lets you transpose like that.  

EDIT:  Yea, I guess ASAP won't do that. 
« Last Edit: April 09, 2014, 10:42:46 AM by kunkel321 »

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,606
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #3 on: April 07, 2014, 09:25:44 PM »
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)

myvlookup.pngWorking with excel row and columns

credit for the code goes here - http://www.mrexcel.c...ell-concatenate.html

IainB

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,141
  • Slartibartfarst
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #4 on: April 08, 2014, 05:19:32 AM »
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #5 on: April 08, 2014, 06:25:47 AM »
The real file has many more records of course.

How many records (rows)?

Is this a one-time task?

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #6 on: April 08, 2014, 07:12:38 AM »
?

skwire

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 4,668
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #7 on: April 08, 2014, 07:17:07 AM »
?

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

rgdot

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 1,880
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #8 on: April 08, 2014, 07:59:49 AM »
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

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,550
    • View Profile
    • Donate to Member
Re: Working with excel row and columns
« Reply #9 on: April 08, 2014, 08:28:27 AM »
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.