Organize scattered, similar cell values into the same columns?

[AGrayson84]

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!!!

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Red	5	6	12	9	6	1
2	Yellow	1	2	6	4	7	1	2
3	Orange	4	2	1	4	6	6
4	Pink	9	11	8	1	12	7	7
5	Blue	2	12	2	5	9	8
6	Black	7	2	4	1	4	3	1
7	Green	5	12	12	11	12	9
8	White	7	11	8	9	1	2	8
9
10
11
12		1	2	3	4	5	6	7	8	9	10	11	12
13	Red	x				x	x			x			x
14	Yellow	x	x		x		x	x
15	Orange	x	x		x		x
16	Pink	x						x	x	x		x	x
17	Blue		x			x			x	x			x
18	Black	x	x	x	x			x
19	Green					x				x		x	x
20	White	x	x					x	x	x		x
Sheet1

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

 

[bobsan42]

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.

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

 

[gaz_chops]

Or another option,

=IF(ISNUMBER(MATCH(B$12,OFFSET($A$1,MATCH($A13,$A$1:$A$3,0)-1,,,5),0)),"X","")

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...imilar-cell-values-into-the-same-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.

 

[AGrayson84]

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

 

[bobsan42]

Glad it worked out for you.
The third column was not really necessary, just a whim.