+ Reply to Thread
Results 1 to 13 of 13

Sorting data from a table with identical column headings

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    8

    Sorting data from a table with identical column headings

    Hello All,

    I have a spreadsheet that I get each week which is filled with data. However there are duplicate column headings and the data is spread out across the spreadsheet making it difficult to retrieve or graph/trend any data.

    Is there a way to use visual basic to sort this table with multiple duplicate column headings into a table with only single column headings for each sample number?

    I have attached a basic example of what I mean below.

    Example.xlsx

    Thanks
    Stephen

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Registered User
    Join Date
    03-06-2015
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    8

    Re: Sorting data from a table with identical column headings

    Hi Mick,

    I tried this code but nothing happened on the spreadsheet. I edited it a little to go to the right.

    This deletes all the contents of the table except the last column and moves this to the first column. it also kicks back a Run time error 1004 and it highlights the row before the last end if Rng.Offset(-1).Resize(Rng.Count + 1).ClearContents.

    Regards
    Stephen

    Please Login or Register  to view this content.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    If you change the range"RngAc" like that the Range address becomes "B1:to the last column in the sheet/row1, rather that the columns with data in them.
    Try the Commandbutton in sheet 2 of the returned file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-06-2015
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    8

    Re: Sorting data from a table with identical column headings

    Hi Mick,

    Apologies but this doesnt do anything when I open it. If I delete the cells and click the command button nothing happens...

    Stephen

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    No, you need to Reinstall the data from the first sheet, and then click the button.
    The code will modify the data so you don't have any duplicate columns.

  7. #7
    Registered User
    Join Date
    03-06-2015
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    8

    Re: Sorting data from a table with identical column headings

    Mick,

    I must be missing something simple. I copied and pasted the data from the first sheet and clicked the button but nothing changed. I also tried change the numbers in the table on the first sheet and pressing the button, again nothing happens.

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    The only thing I can suggest is to delete that file and download the file again, then just click the Button on sheet2.
    You may be in "design mode", I Don't think so because, I've just downloaded that file and it works straight away.
    If when you click the button there appears 6 small circles around the button , then your in "Design mode"
    If in Design Mode , Click Developer tab then click "Design Mode".

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Sorting data from a table with identical column headings

    Hi .
    . Just in case this very small bit of feedback helps.( I Had saved this File from MIck hoping to go through and learn from it later.)
    , As I noticed Stephen was having problems I quickly tried the code.
    . I find that nothing happens when I click on the Button.
    . If I run the code directly from the Development Window (Alt F11 and then double click on Sheet 2 in the Project Explorer, and then play (F5) ) I find the code always works once. After that it is very inconsistent. I have tried re - down loading many times now.
    . As I mentioned it always runs once. Occasionally it will run a few times (with new data which I paste in from sheet 1 or type in myself ). But eventually It will not work at all, until I re down load the File
    Alan

    I am using XL2007 in Vista
    Last edited by Doc.AElstein; 03-11-2015 at 08:55 AM. Reason: Work rather than Run
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  10. #10
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    Perhaps someone else has some thoughts!!!
    Meanwhile here is a new file with the same data and code in sheet1
    The other alternative is to use the code and the Data in another File of your own and see what happens.
    Attached Files Attached Files
    Last edited by MickG; 03-11-2015 at 08:56 AM.

  11. #11
    Registered User
    Join Date
    03-06-2015
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    8

    Re: Sorting data from a table with identical column headings

    Mick,

    Second file works perfectly. No idea what was happening with the first, double checked the code and everything is the same.

    Thanks very much for your help this. I've played about with it and it works even when I increase the number of columns and rows which is exactly what I need as the amount of data I get can vary.

    I'll mark this is solved now.

    Thanks
    Stephen

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Sorting data from a table with identical column headings

    Quote Originally Posted by Stephen23 View Post
    Mick,

    Second file works perfectly. ......
    Hi Stephen
    . Sorry I could not be much more help.
    . With the new File I get similar results as said in Post #9
    . Glad it is working for you. I was only taking a passing interest.
    Alan

  13. #13
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Sorting data from a table with identical column headings

    Stephen, Please it finally work for you.

    Doc, Thank you for you interest

+ 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: 6
    Last Post: 12-18-2013, 09:11 PM
  2. Creating a pivot table from raw data with both row and column headings
    By marshymell0 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-03-2013, 04:31 AM
  3. Replies: 1
    Last Post: 03-23-2011, 06:49 AM
  4. Sorting data in sheet with frozen, protected column headings
    By Computa_Sez_No! in forum Excel General
    Replies: 1
    Last Post: 05-13-2007, 09:49 AM
  5. Can I invert a table so row headings are now column headings etc
    By Sharon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2005, 04:06 PM

Tags for this Thread

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