+ Reply to Thread
Results 1 to 16 of 16

Tranpose rows and columm

  1. #1
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Tranpose rows and columm

    Hi

    I would like convert my data so that I can make a Pivottable.

    My data look like this
    1.PNG


    What to do? Transpose it?

  2. #2
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    Hi

    Concatenate (=A2&" "&B2) the first 2 columns (say in column C) and it will work fine.

    Stephen

  3. #3
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    Hm but I want my weeks going down aswell

  4. #4
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    Well INDEX & MATCH would work for that. Look up examples or attach a worksheet and me/others could get weeks running vertically.

  5. #5
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    Hi thx for responding.

    http://sdrv.ms/174bUcz

    I cant get all the weeks with the products going vertically? The sum/values for the products/Week is working

  6. #6
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    Does the attached help?

    All I have done is add a formula that will look up the right value for Product & Week.

    Stephen
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    Not really...You have not transpose the products = weeks.

    Say my data is : 52 weeks, 1000 products = 52000 rows

  8. #8
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    That bit is reasonably quick to do ... even with 52000 rows. I will send an example in a few minutes.

  9. #9
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    Here you are. On the TransposeStephen sheet I would normally Copy > PasteSpecial > Values so you no longer see the formula, but I have left them there so you can see my workings (working method). Look at Row 1002 in the TransposeStephen sheet where the formula effectively start. I had to delete all the rows below as it made the file too big to load here, so you will have to copy down for 52000'ish rows.

    Any use to you?

    Stephen
    Attached Files Attached Files

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Tranpose rows and columm

    Hi,

    I've written some VBA code to these kinds of "Transpose" problems. I call them CrossTab To Table conversions. See my example attached. Use the VBA code behind the example on your problem. Hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  11. #11
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    Stephen: It kinda work. But I still have to manually copy paste the first 100 products ?

  12. #12
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    Hi Marvin

    Thx for the Macro...When the input box pop up what do I enter?

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Tranpose rows and columm

    It stops and is asking for the number of fixed columns starting in Col A. I believe your CrossTab table has only 1 fixed column. You might also need to delete the blank Column B.

    Some CrossTab tables have more fixed columns to the left of the repeated data. I created this generalized macro to handle any number of fixed columns.

  14. #14
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    I am not sure I understand. Getting a full list of ProductCodes & Product can be done by extracting from your Product Database or from a spreadsheet that already exists in the business ... or typing it it in a very boring process.

    From that point on you repeat the list 52 times by using the cell refencing e.g. "= A2" in cell A1002 (example). Then if Week number is in C2, you would use "=C2+1" in C1002. For weeks 2-52 these formula then work out perfectly to repeat the listing as you require. (Then remember to copy > pastespecial > values on columns 1 - 3).

  15. #15
    Registered User
    Join Date
    07-08-2013
    Location
    denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Tranpose rows and columm

    I understand now Stephen thx for helping out.

    I got another issue. I need to have complete overview for the values from the stores we have. So i need combine each store with product, week and value.

    I have for example 10 stores and if i have 200 products = 10*200*52 (weeks)=104000 rows ?

  16. #16
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Tranpose rows and columm

    Hi

    The idea will work, it just get a little bigger every time!!

    The following is only a guide to the principle, it can be done in many other ways, so just think whether it works for you.

    Having got as far as doing 200 x 52 (product x weeks), now insert a column in Column A and populate that with "1" and copy the "1" all the way down to Cell A10401 (200 * 52 + 1).

    Then ín row 10402 ...

    Formula in A10402 becomes "=A2+1"; B10402 "=B2"; C10402 "=C2" etc
    Row 10402 can then be copies down (using shift+ PgDn) as far as 104001 and you have 10 stores numbered 1-10.
    I would always copy & paste special values at this point.
    You might then want to highlight Column A and run Edit > Replace "1" with "1 - Copenhagen"; Edit > Replace "2" with "2 - Odense"; etc.

    No big macros, no big functions ... just reasonably big data.

    Do you want it all on one spreadsheet, or do you want tabs for each and a consolidated tab? I cannot answerr that as it depends on your needs.

    Stephen

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Tranpose column to rows with header
    By dennismiro in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-13-2012, 02:13 AM
  2. Tranpose
    By pankajsc96 in forum Excel General
    Replies: 15
    Last Post: 01-27-2012, 01:14 PM
  3. Tranpose columns to Rows
    By beardiebeardie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2008, 12:05 AM
  4. [SOLVED] Columm sums
    By Alex McKenzie in forum Excel General
    Replies: 2
    Last Post: 10-22-2005, 03:05 AM
  5. [SOLVED] Open txt file with more than 256 columns (how to tranpose in rows)?
    By uriel78 in forum Excel General
    Replies: 1
    Last Post: 02-11-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1