+ Reply to Thread
Results 1 to 7 of 7

Count matching columns, give total, compress into 1 line that you can click to open

  1. #1
    Registered User
    Join Date
    11-16-2017
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Count matching columns, give total, compress into 1 line that you can click to open

    My ex-wife showed me how to do this once years ago but I cannot remember how to do it for the life of me after that one time. I don't use Excel, I normally just fix it when it breaks! (IT Support Engineer)

    My customer asked me for a list of applications installed on all computers at their company, which there is a sampling of below. Column A is the machine name, Column B is the application. She was able to show me how to take the whole sheet, and make it so every column B that had the same value, it would take them all and compress them down into 1 column with a + sign to the left of it, and a total of the # of columns that had that match on the right (ie.. it would say 5 for 7-Zip 9.20 in my sample). Then you could click on the + sign and it would expand all of the columns and you could see all 5 of the machines it was installed on, then click the - and it would compress them back down to 1 column. That way I would have only 1 column for each program, a total of how many have that program, and can expand out to see all of the lines if needed.

    Can anyone help me with that? I would appreciate it and help you fix any PC problems you have






    Capture.PNG
    Last edited by LVetula; 11-16-2017 at 01:34 PM. Reason: Formatting on columns sample sucks

  2. #2
    Registered User
    Join Date
    11-16-2017
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Count matching columns, give total, compress into 1 line that you can click to open

    I thought I posted this in General, is the right section for this?

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,293

    Re: Count matching columns, give total, compress into 1 line that you can click to open

    You're right about the formatting... The forum ruins it. So...Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  4. #4
    Registered User
    Join Date
    11-16-2017
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Count matching columns, give total, compress into 1 line that you can click to open

    There you go, but I don't know how to mock up what it is supposed to look like. Row 1-5 becomes 1 row for 7-Zip 9.20, shows the total # of rows that have 7-Zip 9.20 in them as 5, and there is a plus sign on the left that let's you expand all 5 so you can see the different column As

    Then the same thing for ABCpdf.NET 7.0. Compresses to 1 row, shows 13 as the # of rows that have ABSpdf.NET 7.0 in them. And so on for all of the cells in Column B that have the same data in them.
    Attached Files Attached Files

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

    Cool Re: Count matching columns, give total, compress into 1 line that you can click to open

    is that what you want?
    Attached Files Attached Files
    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

  6. #6
    Registered User
    Join Date
    11-16-2017
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Count matching columns, give total, compress into 1 line that you can click to open

    Exactly! Except can column H then display how many columns there are inside each item instead of having to open up the + sign and manually count them? And if it's not easy to explain how to do it all in a post, my girlfriend said she can reverse engineer it for me.

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

    Re: Count matching columns, give total, compress into 1 line that you can click to open

    Quote Originally Posted by LVetula View Post
    Except can column H then display how many columns there are inside each item
    sorry, I don't understand
    there are only two columns

    if you want to count something, drag from the top item or item2 to VALUES field

    or attach example to show what you want to achieve

    if she can do it so do it
    Last edited by sandy666; 11-16-2017 at 08:25 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. [SOLVED] SUM Matching Monthly Columns to Give a Variance
    By HangMan in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-04-2015, 01:03 PM
  2. [SOLVED] Count different words and numbers in same range to give total
    By NewbieinNeed3884 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-15-2014, 11:33 PM
  3. Replies: 4
    Last Post: 06-28-2011, 07:14 PM
  4. Replies: 5
    Last Post: 08-05-2009, 12:49 AM
  5. How do I count shirt sizes and give a total of each?
    By dc112675 in forum Excel General
    Replies: 6
    Last Post: 06-23-2009, 12:41 PM
  6. Replies: 4
    Last Post: 12-12-2008, 01:53 PM
  7. Compare 2 columns then give count
    By George4 in forum Excel General
    Replies: 27
    Last Post: 10-02-2008, 11:43 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