Results 1 to 7 of 7

Thread: Organize scattered, similar cell values into the same columns?

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Organize scattered, similar cell values into the same columns?

    Hi everyone! I've been struggling on and off for the past couple of days trying to find a way to come up with a solution to my problem with a large export of data I have. I will include some pictures with some arbitrary data to help explain what I have and what I'd like to do with the data, in hopes that someone wouldn't mind giving me a little guidance, but I'll try to verbally-explain the problem first.

    I have a list of computer names in column "A", and starting in column "B" I have many columns of software installed, for each of the many rows of computers. The software installed on each computer, starting in column "B", is in alphabetical order. The problem is that no every computer has the same software installed, so though many computers have the same software installed, the software is very rarely aligned with the same software in a given column. This causes an issue with filtering and reporting. For instance, if I want to filter only hosts that have a specific software installed, the results will show only computers with the that specific software in that specific column. If that software I'm filtering for exists in other columns then it gets omitted from the filter.

    So I'm trying to come up with a way to either automatically align all of the same software into the same column; or maybe even automatically-generating a list of all of the various software found in the large range (often 300+ rows, and 50+ columns), placing that list across the top row of the spreadsheet, and then place a marker (maybe like an "x") in the cell corresponding to software installed on each machine in each row........ I'm open to anything that will help organize the software in order to be able to see which machine(s) have a specific software installed on them, whether it be a built-in solution within the tools of Excel than I'm unaware of, VBA, or a formula that I can place on another worksheet that will organize this data better.

    Something that would be worth keeping in mind is that the list of machines, nor the list of software, will be consistent each time I need to generate a report..... and the list of computers and all of the various software is so long that I will not be able to reference the specific cell values to make this happen, since the machines and software will always be changing.

    Here is an example of how the data I have looks. The colors in column A can represent the computer names, and the numbers from column B and beyond can reference the names of the software found on each computer:





    And this is an example of what can possibly work, if there's a relatively easy way to do this:




    I've honestly exhausted all efforts in trying to find a way to give me something to work with, between trying to find some VBA to start with, a formula that would work, or feature other than adding something like Kutools (we're not allowed to install other software/tools on our devices) to my computer. Any help and guidance would be HUGELY appreciated at this point. Thanks so much!!!

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,694
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    try this

    ABCDEFGHIJKLM
    1Red5612961
    2Yellow1264712
    3Orange421466
    4Pink911811277
    5Blue2122598
    6Black7241431
    7Green5121211129
    8White71189128
    9
    10
    11
    12123456789101112
    13Redxxxxx
    14Yellowxxxxx
    15Orangexxxx
    16Pinkxxxxxx
    17Bluexxxxx
    18Blackxxxxx
    19Greenxxxx
    20Whitexxxxxx

    Sheet1



    Worksheet Formulas
    CellFormula
    B13=IF(COUNTIF(INDEX($B$1:$H$8,MATCH($A13,$A$1:$A$8,0),),B$12)>0,"x","")


  3. #3
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    It is not refined and far from optimized, but I think it is always best to structure the data properly and then think for presenting it.
    So my solution is macro based. All data will be taken and stored in a 3 column table. then you can make sense of it any way you like e.g. pivot table.

    Read the commented parts at the beginning of the code.

    Code:
    Option Explicit
    
    
    Sub TransformData()
        
        Const DataSheetName = "Sheet1" 'change this to match your data worksheet
        'I assume column A and row 1 contain computer names and labels respectively - so they will be skipped - adjust your data accordingly for this to work properly.
        'for example simply insert an empty line on top if the first row also contains data, rather than column labels.
        
        Dim wb As Workbook: Set wb = ThisWorkbook
        Dim sh1 As Worksheet, sho As Worksheet
        Dim rng1 As Range, rng2 As Range
        
        Set sho = wb.Worksheets(DataSheetName)
        Set sh1 = wb.Worksheets.Add
        Set rng1 = sh1.Range("A1")
        rng1.Select
        rng1.Value = "Computer"
        rng1.Offset(0, 1) = "Software"
        rng1.Offset(0, 2) = "Installed"
        
        Set rng1 = rng1.Offset(1, 0)
        
        On Error Resume Next
        For Each rng2 In sho.UsedRange
            If rng2.Row > 1 And rng2.Column > 1 Then
                If Len(CStr(rng2.Value)) > 0 Then
                    rng1.Value = sho.Cells(rng2.Row, 1).Value
                    rng1.Offset(0, 1) = rng2.Value
                    rng1.Offset(0, 2) = "YES"
                    Set rng1 = rng1.Offset(1, 0)
                End If
            End If
        Next rng2
        
        Set rng1 = Nothing
        Set rng2 = Nothing
        Set sho = Nothing
        Set sh1 = Nothing
        wb.Save
        Set wb = Nothing
        
        MsgBox "All done."
    End Sub
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    Or another option,

    =IF(ISNUMBER(MATCH(B$12,OFFSET($A$1,MATCH($A13,$A$1:$A$3,0)-1,,,5),0)),"X","")
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,918
    Post Thanks / Like
    Mentioned
    385 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    Cross posted https://www.excelforum.com/excel-gen...e-columns.html

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Mar 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    Thank you both so much for your assistance!!!

    bobsan42's solution works great because I don't need to manually build a list of all of the unique software across row "A", which is a problem with the 50+ pieces of software that will frequently be changing from one report to another.

    All I had to do was remove the "Installed" column since I didn't really need it in my scenario, but it was a perfect solution-- I ran a filter against the software I wanted and I saw all of the hosts that had that software only Thanks again!


    And thanks Fluff, sorry for that-- I wasn't aware of that, and will keep that in mind for the future

  7. #7
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Organize scattered, similar cell values into the same columns?

    Glad it worked out for you.
    The third column was not really necessary, just a whim.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •