+ Reply to Thread
Results 1 to 7 of 7

Transposing irregular repetitive data

  1. #1
    Registered User
    Join Date
    03-07-2016
    Location
    DC
    MS-Off Ver
    DC
    Posts
    4

    Transposing irregular repetitive data

    I have a spreadsheet that is generated in a difficult format to manipulate. A series of reports are generated in sequence in a worksheet. I wish to transpose some of the data from each report into a more usable format. Additionally the data is irregular.

    Below is an example of the data in the spreadsheet.

    Case Number DateReceived DateClosed AgencyDesc
    DI-XX-XXX1 10/01/2014 05/18/2015 Department of Fun
    Allegation Code Allegation Description Disposition
    40 XXXXXXX EEEE YYYY JJJ
    41 YYYYYY HHHHHH
    43 ZZZZZ EEEE YYYY JJJ

    DI-XX-XXX2 10/01/2014 10/02/2014 Federal Fire Department
    Allegation Code Allegation Description Disposition
    40 PPPPPPP YYYYY HHHHHGGGGGG

    DI-XX-XXX3 10/01/2014 10/02/2014 Federal Fire Department
    Allegation Code Allegation Description Disposition

    I want to transpose this data to the following columns:
    Case Number DateReceived DateClosed AgencyDesc Allegation Code Allegation Description Disposition

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Transposing irregular repetitive data

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-07-2016
    Location
    DC
    MS-Off Ver
    DC
    Posts
    4

    Re: Transposing irregular repetitive data

    Hi Alan,

    Thanks for responding. I've attached a sample workbook (workbook 1) and what I'd like it to be turned into (workbook 2).

    Any help on automating this would save me probably a week of work!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Transposing irregular repetitive data

    I think this does what you want
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-07-2016
    Location
    DC
    MS-Off Ver
    DC
    Posts
    4

    Re: Transposing irregular repetitive data

    Stephen,

    VBA is giving me a syntax error when I run it. It highlights the following part of the code:

    Sheet2.Range("A1:G1") = Array("Case Number", "DateReceived", "DateClosed", _
    "AgencyDesc", "Allegation Code", "Allegation Description", "Disposition")

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Transposing irregular repetitive data

    Works for me. Make sure there is a space before the underscore.

  7. #7
    Registered User
    Join Date
    03-07-2016
    Location
    DC
    MS-Off Ver
    DC
    Posts
    4

    Re: Transposing irregular repetitive data

    It works!! Many, many thanks!

+ 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. Replies: 16
    Last Post: 12-04-2017, 04:53 PM
  2. Transposing irregular repetetive data
    By Braedos in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-13-2016, 01:41 AM
  3. [SOLVED] Parsing Irregular but well organized data
    By Taisir in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-16-2015, 12:35 AM
  4. [SOLVED] Fitting data into fields out of an irregular list
    By vijaijohn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-07-2014, 01:43 AM
  5. Calculating Weekly Average from Irregular Data
    By non-pro in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 06-08-2013, 11:36 AM
  6. Irregular Data Groups In A Column
    By birturkm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2011, 04:36 AM
  7. Sorting irregular data Preferably with VBA
    By daymo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-06-2011, 04:55 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