+ Reply to Thread
Results 1 to 8 of 8

Conversion from columns to rows for a series of selction criteria

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    Hamont, Belgium
    MS-Off Ver
    365
    Posts
    3

    Conversion from columns to rows for a series of selction criteria

    Good afternoon,
    I am Marc and I am having a rather urgent problem. I have added an xls sheet which provides an overview of a series of equipment which is yes or not used by a specific unit / series of units. Not all specific units have all of the equipment in their inventory. Hence some of the fields are empty or have a zero.
    Now what I would like to do is to have some kind of a report which turns the specific unit (which are now displayed in a row) to a vertical column. I was able to do this using transpose. But I would like to see that on top of each of the units the row heading would state "Equipment" and "Number - #" next to it BUT only for those cells and equipments types where the cell crossing of equipment & unit has been filled. I have been looking at YouTube videos and tried AGGREGATE, MATCH, INDIRECT, etc, etc for one and a half days now and I am getting very desperate.
    I would welcome any hands on solution as I need to apply this 'magic formula' to fifteen other xls sheets which accounts for thousands of lines. If I would need to do this manually I would be in there for days with a serious amount of risk for mistakes.
    Thanks for your earliest consideration.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conversion from columns to rows for a series of selction criteria

    Hello Marc and Welcome to Excel Forum.
    The following formula seems to do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-15-2019
    Location
    Hamont, Belgium
    MS-Off Ver
    365
    Posts
    3

    Re: Conversion from columns to rows for a series of selction criteria

    Good morning, I think I was able to upload my xls file. The issue resides in the first worksheet with the black tab.
    Thanks again for your assistance and help!
    Best
    Marc

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,396

    Re: Conversion from columns to rows for a series of selction criteria

    I see no charts or pivots here, so I'm moving the thread to a more appropriate section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,396

    Re: Conversion from columns to rows for a series of selction criteria

    Quote Originally Posted by Marcellus2019 View Post
    Good afternoon,
    I am Marc and I am having a rather urgent problem. ...
    Thanks for your earliest consideration.
    Administrative Note:

    Just as a cautionary comment, please be advised that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and do not need to take any notice of the urgency of your request if they decide to help you. Please do not try to put pressure of time on anyone here, therefore: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

  6. #6
    Registered User
    Join Date
    08-15-2019
    Location
    Hamont, Belgium
    MS-Off Ver
    365
    Posts
    3

    Re: Conversion from columns to rows for a series of selction criteria

    Thank you. I was getting direct assistance from JeteMc in the States who helped with the original thread. As I was still faced with issues, JeteMc asked me to upload the trouble file. I hope once moved, he will be able to locate it.
    Best regards
    Marc

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,396

    Re: Conversion from columns to rows for a series of selction criteria

    Sorry? What is that comment meant to mean?

    This is a public forum: everybody can see your thread. My note was to do with the nature of the free help you get here and the fact that you may not put any time constarint on that help. Does this make sense?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conversion from columns to rows for a series of selction criteria

    Marc, Ali and the rest of the moderators do a wonderful job of keeping this forum running as smoothly as possible. Please be respectful of their decisions and take their comments as constructive.
    As to the formula try the following in cell B131 (then drag across and down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I tested and it seems to work. The file is large, so I am going to wait and see if you need me to upload before attempting.
    Let us know if you have any questions.

+ 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. Conversion of function from Rows to Columns
    By tomassitoo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-29-2019, 06:35 PM
  2. Conversion and sorting of function from Rows to Columns
    By tomassitoo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-27-2019, 09:59 AM
  3. Replies: 3
    Last Post: 01-18-2018, 12:20 PM
  4. return 100% or 0% if criteria is met in a series of columns
    By photosko in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2013, 02:34 PM
  5. Fill Series of Disjointed Selection across Rows & Columns?
    By mariecpu in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-01-2008, 01:04 PM
  6. [SOLVED] Continuous conversion of data from rows to columns
    By Karaman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2005, 12:06 PM
  7. Continued Conversion of data from rows to columns in a macro.
    By Karaman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-03-2005, 12: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