+ Reply to Thread
Results 1 to 19 of 19

Need to Sort Column Data - Horizontal Sort Not Working

  1. #1
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Need to Sort Column Data - Horizontal Sort Not Working

    Sorry in advance for the long explanation; I don't know how to explain this succinctly:

    I have an Excel worksheet with 106 rows of contact data (plus a header row) and data on those contacts in columns A through S.

    Columns I through S contain data "tags" that were applied to my contacts by Infusionsoft, my CRM program. However, Infusionsoft doesn't separate my tags into individual columns when I export to a spreadsheet; rather, it gives me one massive column containing all my tags separated by commas. So I used Excel's Text-to-Columns function to expand the tag data from the original single column into 11 columns.

    Because Infusionsoft doesn't sort these tags in any order and just dumps them all into a single column of data, once I use Text-to-Columns to separate them out, many instances of the same tags appear in more than one column. Example: one tag is called "Behavior -> Card Opt-in NDCEL 2016" and has been applied to a number of contacts. But some instances of this tag appear in column I, some in column J, some in column K, etc.

    I'm trying to sort all instances of each particular tag into the same column while preserving the rest of the data in the rows. So, using the above example, I want to sort my data so that all instances of the tag "Behavior -> Card Opt-in NDCEL 2016" are sorted into one column, and likewise with each of the other tags.

    I've been looking online all day and know there must be a way to do this, but I can't figure it out. I even found someone else asking my question online who included the attached simple diagram, but they got no answers. I would very much like to know how to do this, as it will greatly help me to analyze data on our Infusionsoft contacts. Help!

    Data Sort Question.JPG

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    San Francisco
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,163

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Would this work? Use IF combine with COUNTIF function with lookup letters in row 5 to determine if the letter exists.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Quote Originally Posted by JieJenn View Post
    Would this work? Use IF combine with COUNTIF function with lookup letters in row 5 to determine if the letter exists.
    Hi, thanks for your reply, and my apologies to the experienced formula users out there, but I'm not familiar with how to do what you're describing. I basically know how to select cells, rows, and columns and use Excel's built-in functions, for the most part. Sorry to ask you to dumb down your explanation, but if you could, that would be great. Would it help it I added my document?

  4. #4
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Okay, the forum doesn't want to allow me to attach my document either, unfortunately... I'm looking at what you attached and see that what you did worked, but I'm not clear on how to use it.
    Last edited by Jadd; 10-31-2016 at 06:44 PM.

  5. #5
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Okay, now I'm seeing what you did and think I get it... but if I'm correct, I have to place the exact data tag I want each column to be sorted by at the top of that column... correct? Not the end of the world if this is the only way to do it, but I thought there must be a way to ask Excel to search and compare the data tags and sort them into separate columns on its own. If not, I'll try your way...

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    1. type what you want to type
    2. go to the bottom of message
    3. click Go Advanced
    4. look up for Manage Attachment and click
    5. click Browse and select your file
    6. click upload
    7. click Close this window

    8. and of course Submit....
    Last edited by sandy666; 10-31-2016 at 06:54 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Trying to attach my document...
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Are you sure you want it formatted like that, there is 35 unique tags.

    Behavior -> Card Opt-in NDCEL 2016
    Behavior -> Downloaded eBook Conference Drip
    Behavior -> Downloaded eBook Educator Web Lead
    Behavior -> Downloaded Young Adult Success Conference Drip
    Customer Tags -> 7M Academy Teachers
    Customer Tags -> Blog List Warm
    Nurture Tags -> Blog signup form
    Behavior -> Self-Opt-in NDCEL 2016
    Behavior -> Downloaded Young Adult Success Conference Drip
    Behavior -> Card Opt-in NDCEL 2016
    Behavior -> 7MA Teachers Clicked to Take Survey 2016
    Behavior -> Card Opt-in Character Ed 2016
    Behavior -> Card Opt-in GAESP/MSP 2016
    Behavior -> Downloaded eBook Conference Drip
    Behavior -> Downloaded NYT Teaching Peace Conference Drip
    Behavior -> Self-Opt-in Character Ed 2016
    Prospect Tags -> 7MU 2016 Invite List
    Prospect Tags -> Metro ATL School Administrators
    Starting Tags -> Cold SF Lead Email Drip Start
    Customer Tags -> Blog List Warm
    Starting Tags -> 7MA Teachers Survey Email Sent 2016
    Prospect Tags -> David Craig Leads
    Prospect Tags -> Mashburn Invite List
    Starting Tags -> Arnall open house invite sent
    Prospect Tags -> SalesForce Academy Cold Leads
    Behavior -> Opened 2016 Teacher Survey
    Behavior -> Clicked to Watch PF Kinetic Video Conference Drip
    Starting Tags -> Mashburn open house invite sent
    Starting Tags -> March Madness Opeb House Drip Sequence Start
    Prospect Tags -> GAESP conf. attendee
    Starting Tags -> Past Conference Attendee Email Drip Start
    Prospect Tags -> Metro ATL School Administrators 2
    Behavior -> Opened Mashburn Invite

    Seems like a lot of scrolling...

    How about a header with the tag and a simple yes/no or something along those lines?

  9. #9
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    You're right, TheN, there are a lot of separate tags... I would be happy with what you describe, if there is an automated function to get there. What I was not looking for was spending a ton of time having to manually go through and cut and paste everything... is there a formula or function to achieve what you're describing?

  10. #10
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    If you know the max ranges it can be automated. But you'd have to use an array formula to the best of my knowledge. And those are very resource hungry.

    If your sample size is illustrative of what you're working with yes it can be done.

    If this is just a small portion of your real data my method will run extremely slow and you are better off trying vba

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    See attached Sheet1:

    Copy /paste columns A:E from "export" sheet

    Column F onwards have TAGS

    in F2:

    =IFERROR(IF(MATCH(F$1,'export_2598-27'!$F2:$ZZ2,0),"yes",""),"")

    Copy across TAG columns and down rows
    Attached Files Attached Files
    Last edited by JohnTopley; 11-01-2016 at 08:07 AM.

  12. #12
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    TheN - This is a decent representation of the typical data size. Can you elaborate on how to automate using the formula you describe?

  13. #13
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    JohnTopley - thanks for posting this. However, I looked at the sample version you provided, and I see that the formula did not locate all instances. For instance, if you do a Find for the TAG "Behavior -> Downloaded eBook Conference Drip" in the original document, you'll see more than ten instances. The sheet with your formula only found two instances of that tag. I really appreciate what you did and actually tried it, but then realized it wasn't showing all the instances of certain tags, so... not sure how to proceed.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Anything wrong with my post #11 which avoids array formulae and does (I believe) what TheN was alluding to?

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    The probem is the data as the "missing" items have a leading blank so

    "Behavior -> Downloaded eBook Conference Drip" is actually " Behavior -> Downloaded eBook Conference Drip"

    hence no match with TAGS.

    The formulae work fine!!!

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Try

    =IFERROR(IF(MATCH(F$1,TRIM('export_2598-27'!$F2:$ZZ2),0),"yes",""),"")

    Enter as before.
    Attached Files Attached Files
    Last edited by JohnTopley; 11-01-2016 at 02:04 PM.

  17. #17
    Registered User
    Join Date
    10-31-2016
    Location
    Albuquerque, New Mexico
    MS-Off Ver
    2013
    Posts
    9

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Quote Originally Posted by JohnTopley View Post
    The probem is the data as the "missing" items have a leading blank so

    "Behavior -> Downloaded eBook Conference Drip" is actually " Behavior -> Downloaded eBook Conference Drip"

    hence no match with TAGS.

    The formulae work fine!!!
    Aha! You're right! Damn Infusionsoft for putting unwanted spaces in front of my tags that I didn't notice... THANKS SO MUCH for the help!

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Common problem!!!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  19. #19
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Need to Sort Column Data - Horizontal Sort Not Working

    Hi, simply change it to "*"&whatever&"*". It will fix the non-matches.

    I was at work, but it seems you've already gotten a working solution. Good job.

    Edit:

    I was alluding to setting up an array to extrapolate the 35 unique things instead of manually getting them first, that's about all. That's very resource hungry, and since this isn't something that's not going to be done and redone over and over, I'm not going to waste my time with it. Arrays confuse me still, especially after a long day of work

    It doesn't help that the data has blank spaces all over the place either, makes it super involved, at least with my range of knowledge.
    Last edited by TheN; 11-01-2016 at 08:33 PM.

+ 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: 1
    Last Post: 03-04-2015, 02:57 PM
  2. [SOLVED] I have a sort macro. How to add script to preselect rows to sort based on column value?
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2014, 02:25 AM
  3. Replies: 2
    Last Post: 01-15-2014, 09:31 AM
  4. [SOLVED] New to arrays-BubbleSort code(Working)-How do I edit to sort by 2nd column then 3rd?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-10-2013, 06:29 AM
  5. Replies: 5
    Last Post: 04-25-2013, 11:50 PM
  6. Replies: 3
    Last Post: 05-17-2009, 04:31 AM
  7. [SOLVED] How do I sort a column of data and have each data row sort accordi
    By Oedalis in forum Excel General
    Replies: 1
    Last Post: 03-17-2005, 08: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