+ Reply to Thread
Results 1 to 53 of 53

Transposing Data

  1. #1
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Transposing Data

    Hi all,

    I have a .csv file that is formatted in rows and I need to transpose it in an excel spreadsheet into columns. I have attached the .csv file as an example and a sample of how I would like the data transposing.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Which sheet do you wan the result in?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glenn,

    Thanks for your rapid response.

    Sheet 2 if possible on the Data in Columns Template.

    Would be good If I could sort / separate by department (department is not included in the .csv but I could easily add it) and employee

    i.e. All employees in Department 1 with a with totals per employee separating each in employee in the department and then departmental totals at the end of each department.

    Hope that makes sense

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    In D10, copied across and down, use this array formula:

    =IFERROR(INDEX(Sheet3!$E$1:$E$171,MATCH(1,(Sheet3!$A$1:$A$171=Sheet2!$D$3)*(Sheet3!$C$1:$C$171=$B10)*(D$7&" "&D$8=Sheet3!$D$1:$D$171),0)),"")

    You need to make sure that the yellow cells are an EXACT match for the data on your CSV (which I copied in as sheet 3).

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    one more Q. Do you NEED backward compatability with Excel 2003 (your profile says Excel 2010, but you're using th eold .xls extension.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    If this is what you want, it's also easy to make D3 a dropdown, so you can select the desired employee.

  6. #6
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    What the!

    How the heck did you master that so damn quickly, I've been racking my brains for days on this one. I really like your solution.

    Is backward compatibility an issue?

    With respect to D3 Employee drop down, yes, that would be a cool feature ....
    or
    Directly under (say row 44) start again with the next employee in the department.

    And if we could get really fancy, a departmental total at the end of the report

    Really appreciate you speed on this, it's quite fascinating

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    And now you have a dropdown. I created a list of unique names (using a Named range "list"). The formula will auto-expand as much as is needed. I then used a second Named range ("names") and data validation to set up the dropdown.

    To view the formulae used in the Named ranges, hit Ctrl-F3.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Regarding backward compatability. No, I can fix it so that it will work in Excel 97-2003, but the array formula will be a liltle more than 2X as long, like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Nah, don;t worry about it then. I'm pretty sure I'll be using onwards of 2003.

    Thanks heaps.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  11. #11
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Sweet.

    Just a thought.

    The Column B. I populated that column with dates to try and help me resolve the issue. I don;t suppose there is any way of leaving column B blank on the template and then populating column B from the date range in Sheet 3?

    Might be asking too much but I thought I'd ask anyway.

  12. #12
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Will do Glenn,

    Sweet.

    Just a thought.

    The Column B. I populated that column with dates to try and help me resolve the issue. I don;t suppose there is any way of leaving column B blank on the template and then populating column B from the date range in Sheet 3?

    Might be asking too much but I thought I'd ask anyway.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Yep. But it's beer o'clock here. (19:30, I'm not an afternoon boozer: I'm in Asia right now). I'll sort this in the Thai morning.

  14. #14
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Nice one. It's well past beer o'clock here , 11pm so I'm gonna call it a night, my brain hurts but at least I'll sleep tonight knowing the problem has been resolved, oh what a joy

    Enjoy your stay in Asia.

    Communicate soon.

    P.S Shall I leave this post open for now or do as you requested and mark it as solved?

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Array formula to return the date, copied down:

    =IFERROR(INDEX(Sheet3!$C:$C,SMALL(IF(Sheet3!$A$1:$A$171=Sheet2!$D$3,IF(Sheet3!$D$1:$D$171=D$7&" "&D$8,ROW(Sheet3!$A$1:$A$171))),ROWS(D$10:D10))),"")

    Array formula to return the rest, copied across and down:

    =IFERROR(INDEX(Sheet3!$E:$E,SMALL(IF(Sheet3!$A$1:$A$171=Sheet2!$D$3,IF(Sheet3!$D$1:$D$171=D$7&" "&D$8,ROW(Sheet3!$A$1:$A$171))),ROWS(D$10:D10))),"")

    Remember:Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glenn,

    I have been "playing" with the spreadsheet to try and format it a little tidier and make it more efficient - Hope that is OK

    I have used the Data Connection dialog box to amend some properties (hope that's the right place) so that whenever the workbook opens it refreshes the data set from the csv file called "its IT-SL Swipe Card Export.csv"

    Aha, the date thing you have formulated looks good, but I think that has now created another issue that I did not anticipate, it has cut out the weekends

    Maybe, I should just stick with the dates (from and to) that are the earliest and the latest on the sheet "its IT SL Swipe Card Data".

    On sheet "SL Swipe Card Report" could cell B1 pick up the earliest date from sheet "its IT SL Swipe Card Data" and cell B2 pick up the latest date from sheet "its IT SL Swipe Card Data", then could column B, starting in cell B6 populate all of the dates between those two dates in cell B1 and B2 and populated the column B (including the dates in between i.e. the weekends)

    I've attached the its IT-SL Swipe Card Export.csv file that gets re written depending on weekly reporting or monthly reporting or whenever I want to run the report for a date range, so is constantly changing in data size etc (that is why I checked the radio button "If the number of rows in the data range changes upon refresh" - Insert cells for new data, delete unused cells" - I guess that is the right option but happy to be corrected if I'm I'm the wrong track

    I've also attached the workbook that you've helped me with and that I have been "playing" with a little.

    One thing, Could I also hide the sheet "its IT SL Swipe Card Data" and also hide all of the formulas in all of the cell and sheets and maybe even protect the data so that it cannot be manipulated and only contains a replication of the data from its IT-SL Swipe Card Export.scv?

    Oh, and I have also fixed the typo's that I had in the column headers to match the its IT-SL Swipe Card Export.csv file.

    Cheers,

    Stewie.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    OMG. Quite a shopping list.

    I can't refresh your data. Reload your file with the data in place.

  18. #18
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    OK. I've turned off "refresh data on opening file"

    FYI. The its IT-SL Swipe Card Export.csv file is located in the C:\New Folder\its IT-SL Swipe Card Export.csv if that helps.

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Before protecting/hiding; make sure this is OK. I have set up a pile of named ranges that will allow the formulae to expand/contract as needed (up to 1000 rows of raw data); to keep it felxible. Each different shaded colour is a new/amended formula. Check it over.

    The raw data sheet can be hidden easily. Do you also want all the working formulae protected from tampering on the results sheet?
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    *** Sorry Glenn, I attached the wrong file, this is the correct one with the sheet protection****

    Looks awesome Glenn, I was actually doing some formatting while you were hard at work so I didn't feel left out

    What you have done looks excellent

    OK I have hidden the sheet its IT SL Swipe Card Data but if I also protect it with a password then the data on sheet SL Swipe Card Report will not refresh so for now I've just hidden it without protecting it (unless you know of another way)

    I have also hidden and locked the cells B6:R38, and hidden cells B1:B2 and locked the Cells A1:A2 and C1 on sheet SL Swipe Card Report and then protected the sheet with the password 1234.
    Attached Files Attached Files
    Last edited by [email protected]; 02-28-2017 at 12:34 AM.

  21. #21
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Damn,

    Try this one. Arrghhhhh.
    Attached Files Attached Files

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Away for a while. Back later....

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    looks great. I always just hide sheets that are "active". No doubt there is a way to do this. I suggest that you open a new thread to address that. Is there anything else needed here on this sheet?

  24. #24
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Yeaaaaaah ........

    The spreadsheet works great for the 31 day period (or less) ....... however ..... what if I want to run the report for a longer period, say a quarter or even a year. If I copy the formulas down for the maximum period of time say a year, then the totals will always be down at row 365 (ish) regardless of how much data is exported in the .csv file. Is there any easy way of adjusting the report row length according to the date range exported in the .csv file?

    i.e. if only 7 day period is exported then only 7 rows populated with totals directly underneath; 30 day period is exported then only 30 rows populated with totals directly underneath; 90 day period exported then only 90 rows populated with the totals directly underneath etc, etc?

    Cheers,

    Stewie.

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    that might need VBA...

    The obvious workaround (the one I always use) is to put the total at the TOP. Saves you scrolling all the way down...

  26. #26
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Aha, good thinking about the totals at the top. I'll try that.

    Is VBA to much outside the scope of this forum?

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Not at all. But it's outside my scope!!!

    If you want to go that way: close this thread and start another. It's a separate problem.

  28. #28
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Thanks Glenn,

    You have been amazing.

    So now I need to close this thread with "solved" and "recommend" you as the solution provider or something like that anyway, correct ?

  29. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    To mark as solved, go to your first post. Up there at the top of your post, select "thread tools". It's in there. To Add Reputation, just hammer the Sherrif's badge at the bottom left of any of my posts.

  30. #30
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Will do Glenn,

    BTW something glaringly obvious has just come to me relating to my last question re: totals for all employees.

    Would it be possible to amend the formula so that the drop down list of employees includes the option for "All Employees" and then have the table below it add up the totals for the period for ALL employees. This would, I think be an excellent work around, what do you think?

  31. #31
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Should be do-able. I'm off now... in any event, I'd need to let the notion ferment in my head for an hour or two. Look back later.

  32. #32
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glen, I've marked the thread as excellent and gave you "Add Reputation" on the first post but now when I try to add more reputation on other posts youve replied with I get this message "You must spread some Reputation around before giving it to Glenn Kennedy again."

  33. #33
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Thanks Glenn. I hope that is not the only fermenting going on and that you are also enjoying a cool fermented beverage of your choice

  34. #34
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Doh, I think I've broken it !!!!!!

    I've tried to get a little more fancy and add a column (Department) and include in the formula and spreadsheet that you configured for me without bothering you ...... however ....... here I am bothering you agin as I think I've stuffed it up.

    What I'm trying to achieve on top of the ALL Employee option, is the option to filter the list by Employee and/or Department. I've amended the export that generates the .csv (see attached) and I've attempted to amend the formulas and named ranges on the work book to get the name and departments function to work (see attached)

    Hope this find you well and if you could cast your eyes over it for me or steer me in the right direction it would be greatly appreciated.

    Cheers, Stewie.

  35. #35
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    The Forum won't let you go daft awarding rep. Others need to get some, too. Anyhow. take a look. I now have an "All Staff" option. I'll leave it to you to reprotect your sheet, etc.

    Just about to wander downtown for a beer and dinner!!
    Attached Files Attached Files

  36. #36
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    Our posts passed each other somewhere mid-Pacific. Does my Post sort you out?

  37. #37
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Have a great wander Glenna and enjoy your beer and dinner.

    Thanks for the All Staff update, however it doesn't appear to be adding up all of the staff for all of the Types i.e the DAy Base for 03/01/2017 - 31/01/2017 for All staff should be a rather large number.

    Also I just sent you another message regarding that I think i've stuffed up the spreadsheet

    As for sharing the love with awarding Reputations, I'm not a regular on this forum and don't ask for help often so hard for me to share the reps around

    I may ask some other forum questions so I can rep them and them re rep you

    Have a great evening, are you on work or pleasure in Asia?

  38. #38
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    OK. I'll look at this in the am. Mostly here for fun, but doing some www based consultancy work (unrelated to Excel)...

  39. #39
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Sounds interesting www work. Do you mean website work?

  40. #40
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    You were correct. A different (normal, non-array) formula is in use now.

    Regarding consultancy: no it's food safety issues. Google my name and the word "residues". It'll pull up endless pages and pages of "stuff". Yawn. Mostly history, now though.

    I forgot about the Dept bit. Is it possible to add this to your imported CSV to the RIGHT of previous data (I'm lazy and don't want to have to move all the column references around!!).
    Attached Files Attached Files

  41. #41
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Aha, many years ago I qualified as a Food Science and technologist. Many years ago

    yeap, I can either eidt the export .csv ..... or ..... if you like, if you give me a little direction I could have a bash at amending the column references, could be good practice? Totally up to you though Glenn.

    Have a great day.

  42. #42
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    I was once described as a frivolous pleasure seeker.

    Nowadays, I do that on a more-or-less full time basis. Will look back lter when am back at the Laptop.

  43. #43
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Thanks Glenn.

    I may be off line for a while as I'm coaching kids sport, but I'll be back about 8pm South Australia time.

    Chat later.

  44. #44
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    and I'been earning some money... I started on this, but I'm not exactly sure how you want the areas bit to work.
    Check this over. Is it what you'd intended?
    Attached Files Attached Files

  45. #45
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glenn,

    Hope you are well. There appears to be a slight problem with the Swipe card report template. All work OK if I select "All Areas" but if I only select one department it doesn't populate or refresh the data.

    Also I'd also like to add the fields:-
    - Brv.Lve
    - Pub Hol
    - Hol Lve Lo

    Which I have already added to the export file. Hope that makes sense, chat soon,

    Stewie.

  46. #46
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    OK. See the problem. Back soon....
    Last edited by Glenn Kennedy; 05-29-2017 at 12:30 PM.

  47. #47
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    OK. Simple enough. A couple of column references hadn't been $d. One redundant sub-clause also removed. are you likely to want to add many more additional fields??
    Attached Files Attached Files

  48. #48
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glen,

    Thanks for having a look at the spreadsheet for me.

    I have had to make a couple of changes as the when I was importing the data it produced two columns of data for department and the Unique Area lookup was looking a the wrong column (G), I think I've managed to amend the range and Named Range by myself and now have that part of the sheet working OK but now the Area drop down doesn't seem to work again and I cannot seem to get it to work

    I have also added the extra columns in the spread sheet and I'm pretty sure that is all that will be require to be added.

    I've attached the spreadsheet (that I have attempted to amend) and the latest .csv file for you.

    Thanks heaps again.,

    BTW, the location of both of the files needs to be X:\Report

    Stewie
    Attached Files Attached Files

  49. #49
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glen,

    Hope you are well. Just seeing if you have had a chance to have a quick look at the spreadsheet for me. I thought I was able to make the amendments myself and not bother you with it however all the amendments I made didn't seem to resolve the issues completely 100% and I'm at a loss what to look. If you could either guide me in the right directs to reference the $'s, which redundant sub-clause to remove ..... I'll giv e a bash myself. Always keen to learn.

    Thanks heaps.

    Stewie.

  50. #50
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    ****Update Glen****

    I think I may have solved the issue I'll confirm after I've done some more testing

  51. #51
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transposing Data

    OK. I'll not be here much today...

  52. #52
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    NO worries Glen, Looks like I may have sorted it out.

  53. #53
    Forum Contributor
    Join Date
    10-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016 on 365 Subscription (Win 10)
    Posts
    134

    Re: Transposing Data

    Hi Glen,

    Hope you are well. Before I post this to the forum, I thought I would run it past you first just in case you had the time to have a look at this for me and if it was an easy solution for you to assist me with.

    Following on from the previous spreadsheet that you assisted me with I have been trying to knock something together for my kids Gymnastics Club where I volunteer as a coach and judge.

    Basically this is a form (x4) that we need to hand in at competitions with the gymnasts names, competing order, Level, Under (Age), Order etc. Normally these are hand written on the printed spreadsheet but this year I'd thought I'd try and get a little fancy to make the process far less stressful for the coaches by attempting to automate some (if not all) of the process. I have just about completed the spreadsheet however I would like to add a few extra "smarts" if they are possible.

    The first "smart" I'd like to add would be the ability to limit the "Data Validation" list in the "Name" cells based on the "Level" and "Under" criteria. i.e. if Level 4 Under 9 is selected then I'd like the list of names to be limited to ONLY the Level 4's Under 9's in the list of names from the Sheet [Gymnasts], *Note, no need to worry about "Name" (Leaps) as this is not always used in comps and I can just add this independently as it's not always part of the process.

    The other "smart" i'd like to add is that the "Order" cells on the sheet [Competing Orders] be auto filled based on the Group selected in cell F3. This may be a little complicated however it goes like this .....
    Group 1 = Vault 1, Bars 2, Beam 3, Floor 4
    Group 2 = Vault 2, Bars 3, Beam 4, Floor 1
    Group 3 = Vault 3, Bars 4, Beam 1, Floor 2
    Group 4 = Vault 4, Bars 1, Beam 2, Floor 3
    I have created a matrix for these on sheet "Gymnasts N2:R5

    ****Update v1.3*****
    I have been doing some more testing and playing around with formulas etc and made some pretty good headway (well for me anyway)

    I have managed to resolve the "smarts" for :-
    Group 1 = Vault 1, Bars 2, Beam 3, Floor 4
    Group 2 = Vault 2, Bars 3, Beam 4, Floor 1
    Group 3 = Vault 3, Bars 4, Beam 1, Floor 2
    Group 4 = Vault 4, Bars 1, Beam 2, Floor 3
    Which I am very pleased with.

    I have also made some good headway with the "smarts" for limiting the Data Validation on the drop down list in the Name Column I have managed to write a formula that limits the name list based on the Level 4 or 5 ...... now my brain is really hurting now as I try to work out how to add the additional criteria "Under" to limit the list to say Level 4 Under 9.

    Anyway, I have attached the updated spreadsheet for you just in csae you have time to cast your eyes over it and possibly point me in the right direction.

    One other thing I'd like to happen is that the data in the Name and Gymnasts No columns clears itself if I change the Level or the Under Cells, if you get what I mean. Currently if I select a Level 4 or 5 the Name Date Validation drop down list will limit to either level 4 or 5 respectively and I can chose a name from the limited list ..... however if I then go and change the Level to another level the names already entered in the Name Column remain which make the data look like it is out of integrity. If possible (and I doubt this is possible) I'd like the Name column to ONLY remain as it is if the Level cell and the Under Cell match the list that is selected and maintains data integrity. If either of the Level cell or Under Cell are changed which would cause the names already entered in the name column to be out of integrity then I'd like some kind of warning ... or .... for the data in the name column to simply be cleared. Not sure if this is even possible or not, but it would be a nice feature
    Last edited by [email protected]; 06-11-2017 at 11:11 AM.

+ 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. [SOLVED] Need help with transposing data
    By harryk21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-01-2015, 12:46 PM
  2. Transposing repeating vertical data to horizontal data but retaining unique values only
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 12:57 AM
  3. Transposing list of values with 6 zeroes in front to retain the zero after transposing
    By Lisa4legin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2013, 03:34 AM
  4. [SOLVED] Transposing data and more?
    By rjmcmillen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2012, 05:58 PM
  5. Replies: 5
    Last Post: 04-23-2010, 08:01 AM
  6. Transposing data
    By flds in forum Excel General
    Replies: 3
    Last Post: 06-21-2008, 07:11 AM
  7. Replies: 5
    Last Post: 08-01-2006, 12:23 AM

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