+ Reply to Thread
Results 1 to 18 of 18

semi repeating data in columns to arrange in rows

  1. #1
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Smile semi repeating data in columns to arrange in rows

    I have a table with repeating headers going down e.g.

    Name: Silvi
    Status: Active
    Email: silviemail
    City: Pretoria

    Name: James
    Status: Inactive
    Email: jamesemail
    City: New York

    Name: Elos
    Email: elosemail
    City: Texas


    I need it to go horizontally with the answer below them:

    Name Status Email City
    Silvi Active silviemail Pretoria
    James Inactive jamesemail New York
    Elos elosemail Texas

    The other issue is that some of the 'groups' are missing details e.g. some omit the Status header and info alltogether so they dont all have the same details.

    IS there anyway around this? Please

  2. #2
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: semi repeating data in columns to arrange in rows

    Is "Name: Silvi" in one cell or two?

    Might be easier to help if you could attach a dummy workbook showing the exact data layout.

    Beth.

  3. #3
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Hi Beth, thank you for replying.

    "Name" is in one column and "Silvi" is in the second column.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: semi repeating data in columns to arrange in rows

    Please attach file representative of the problem. No one wants to create "fake" data from scratch only to find that it is not representative.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

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

    Re: semi repeating data in columns to arrange in rows

    Based on each set of data having a blank row between them , try this for results starting "D1".
    Please Login or Register  to view this content.
    Regards Mick

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

    Re: semi repeating data in columns to arrange in rows

    To Cater for Group 3 and similar try:-
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Thank you I have attached the data for you.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Thank you, I have attached the spreadsheet. thank you so much
    Attached Files Attached Files

  9. #9
    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,900

    Re: semi repeating data in columns to arrange in rows

    I think this does what you want,BUT... it may break down if your list is very long. Try it. Happy to explain what's happening if it works... otherwise forget it!!!!
    Attached Files Attached Files
    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

  10. #10
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Quote Originally Posted by Glenn Kennedy View Post
    I think this does what you want,BUT... it may break down if your list is very long. Try it. Happy to explain what's happening if it works... otherwise forget it!!!!
    Thank you! It works! The data is very large but I will see how far i can take it. The only thing is that I don't know how to extrapolated further down the second worksheet "ideal table" i.e. it stops filling in fields after a few tables. I continued the formula down column D to continued the padded identity list on the first sheet, so that is perfect but I tried copy the formulas down but that does not work. Maybe there is another way i should be doing in on the "ideal table" sheet? Thank you again in advance!

  11. #11
    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,900

    Re: semi repeating data in columns to arrange in rows

    Away from PC. So I can't easily see what I did!! Change the range of the INDEX formula to match the last cell in the single column of padded data on the first sheet.

  12. #12
    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,900

    Re: semi repeating data in columns to arrange in rows

    I'm certain the previous one will break down. This one won't.

    Add "Agent Name:" below the last entry in the column. Then a gentle formula for column A and a monster in column B. This seems OK, providing every entry starts with "Agent Name:" ... which seemed to me to be a reasonable assumption. The numerical suffix should increment by 14 per row, with gaps where I randomly deleted a row or three.

    In your real data this might throw up a few issues with cells that return text, as opposed to those that return numbers. Take a look and let me know. THOSE sorts of problems are easily fixed.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: semi repeating data in columns to arrange in rows

    I've taken a different approach to Glenn, which does not involve changing your source sheet.

    In the Current Table sheet I've used column C as a helper, with this formula in C1:

    =IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

    You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

    I've also use a helper column in the Ideal Table sheet, with this formula in P2:

    =IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

    This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

    =IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

    which brings the agent name across, and this one in B2:

    =IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

    and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

    Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

    =COUNTIF('Current Table'!A:A,A1)

    to tell you how many records to expect.

    Hope this helps.

    Pete
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Quote Originally Posted by Pete_UK View Post
    I've taken a different approach to Glenn, which does not involve changing your source sheet.

    In the Current Table sheet I've used column C as a helper, with this formula in C1:

    =IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

    You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

    I've also use a helper column in the Ideal Table sheet, with this formula in P2:

    =IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

    This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

    =IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

    which brings the agent name across, and this one in B2:

    =IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

    and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

    Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

    =COUNTIF('Current Table'!A:A,A1)

    to tell you how many records to expect.

    Hope this helps.

    Pete
    Thank you so much. It worked perfectly. You are all geniuses! I am always impressed at how powerful excel is. Thank you again for all the effort. I cant believe how helpful you all are.

  15. #15
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Quote Originally Posted by MickG View Post
    To Cater for Group 3 and similar try:-
    Please Login or Register  to view this content.
    Regards Mick
    Thank you for your help. Unfortunately this is too advanced for me and would not know where to start. I did get a solution from another post. Thank you so much.

  16. #16
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Quote Originally Posted by Glenn Kennedy View Post
    I'm certain the previous one will break down. This one won't.

    Add "Agent Name:" below the last entry in the column. Then a gentle formula for column A and a monster in column B. This seems OK, providing every entry starts with "Agent Name:" ... which seemed to me to be a reasonable assumption. The numerical suffix should increment by 14 per row, with gaps where I randomly deleted a row or three.

    In your real data this might throw up a few issues with cells that return text, as opposed to those that return numbers. Take a look and let me know. THOSE sorts of problems are easily fixed.
    Thank you Glenn for the assistance!

  17. #17
    Registered User
    Join Date
    09-06-2017
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: semi repeating data in columns to arrange in rows

    Quote Originally Posted by Pete_UK View Post
    I've taken a different approach to Glenn, which does not involve changing your source sheet.

    In the Current Table sheet I've used column C as a helper, with this formula in C1:

    =IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

    You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

    I've also use a helper column in the Ideal Table sheet, with this formula in P2:

    =IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

    This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

    =IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

    which brings the agent name across, and this one in B2:

    =IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

    and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

    Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

    =COUNTIF('Current Table'!A:A,A1)

    to tell you how many records to expect.

    Hope this helps.

    Pete
    Thank you it worked perfectly!

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: semi repeating data in columns to arrange in rows

    Glad to help - thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    In future, you should avoid quoting whole posts, as it is just clutter.

    Pete

+ 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] Moving Repeating Data from Rows to Columns (Not Transpose)
    By mifac in forum Excel General
    Replies: 6
    Last Post: 07-30-2021, 04:08 PM
  2. [SOLVED] Moving Repeating Data from Rows to Columns (Not Transpose)
    By sepmir1 in forum Excel General
    Replies: 16
    Last Post: 10-15-2019, 08:23 PM
  3. Convert data from 3 rows to 3 columns repeating
    By vineeth.k11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2019, 10:05 AM
  4. need formula to arrange the data from columns to Rows
    By Giri.hb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2018, 02:10 AM
  5. [SOLVED] Transpose Columns to Rows While Repeating Data in First Column to Each Row
    By leoxanigm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2015, 06:54 AM
  6. Replies: 7
    Last Post: 02-05-2015, 03:20 PM
  7. [SOLVED] Transpose Data/ Re-Arrange Columns and Rows
    By newbieexceldude in forum Excel General
    Replies: 2
    Last Post: 02-19-2012, 08:14 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