Excel sheet sorting

Posted on 2007-08-01
Last Modified: 2010-03-05

I have a excel which has this data

Computername            Software      Software              Software
Dev-chen-mrd100     Citrix                   MS Office          Magic
Dev-chen-nas01        Msoffice             Magic             Citrix
Dev-chen-srv400      Magic                 Citrix                     Nero
Dev-chen-srv401       Nero                 Citrix                     Ie 7.0

I have the above data in the excel.What i want is all the ms office to come to one colum and all the Magic in one colum and if any software is not found in another row then leave the box empty.

Question by:bsharath
    LVL 80

    Expert Comment

    Here are two formulas to consider if you have a list of software titles in B1:Z1
    =IF(Sheet1!A2="","",Sheet1!A2)                  for cell A2         Copies the computer name over from Sheet1
    =IF(SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$Z$1000=B$1))>0,B$1,"")          for cell B2     Returns software name or an empty string

    You may copy these formulas down and across as needed

    LVL 11

    Author Comment


    Any way with a macro.

    If not then i shall say what i have understood
    At the end of each row
    On the next row this

    LVL 3

    Expert Comment

    In the last column write this formula then copy down (copy by dragging down)
    =IF(COUNTIF(F18:H18,"Magic"), "Magic found", "")
    where F18:H18 is the range (e.g F18 citrix, G18 msoffice, H18 magic)

    Hope this helps.
    LVL 3

    Expert Comment

    If you want to list the name of the computer on which the software exists then
    =IF(COUNTIF(F18:H18,"Magic"), E18, "")
    where E18 in the cell which has the computer name (e.g. Dev-chen-mrd100)
    write the formula in cell I18, considering that Dev-chen-mrd100 is in cell E18
    LVL 80

    Expert Comment

    The two formulas I posted were intended for cells A2 and B2. The formula for A2 may be copied down. The formula for B2 should be copied across and down. Since the formulas return empty strings (looks like a blank) when there are no more data, you can copy them down indefinitely (thereby preparing for more data to be added in the future).

    The reference to row 1000 was arbitrary, and may extend beyond your last data; doing so allows you to add data in the future.

    LVL 13

    Expert Comment

    'this macro will populate all magic softwares in column 2 and ms office in column 3
    Sub agroup()

    For i=2 to Sheet1.UsedRange.Rows.Count
         For j=2 to 4
             if UCase(Sheet1.Cells(i,j)) ="MAGIC" then
                  if j<> 2 then
                          temp = Sheet1.Cells(i,2)
                          Sheet1.Cells(i,2) = "Magic"
                          Sheet1.Cells(i,j) = temp
                  End if
             End if
             if UCase(Sheet1.Cells(i,j)) ="MS OFFICE" then
                  if j<> 3 then
                          temp = Sheet1.Cells(i,3)
                          Sheet1.Cells(i,3) = "MS Office"
                          Sheet1.Cells(i,j) = temp
                  End if
             End if
    End Sub
    LVL 38

    Accepted Solution

    Hi Sharath,

    >  if any software is not found in another row then leave the box empty.

    Here's a routine that creates a column for each software type and only lists an entry for rows that have that type:

    Sub AlignColumnData()
    Dim ws As Worksheet, rng As Range, cel As Range, celrow As Range, fndval As Variant, i As Long

    Dim oDic As Object, ditem As Variant

    Application.ScreenUpdating = False

    Set oDic = CreateObject("Scripting.Dictionary")

    Set ws = ActiveSheet
    Set rng = Intersect(ws.UsedRange, ws.UsedRange.Offset(1, 0), ws.UsedRange.Offset(0, 1))

    For Each cel In rng
        If LCase(cel.Value) = "msoffice" Then cel.Value = "MS Office"
        If Not oDic.Exists(cel.Value) And cel.Value <> "" Then
            oDic.Add cel.Value, cel.Row
        End If

    rng.Cells(1).Resize(1, oDic.Count).EntireColumn.Insert Shift:=xlShiftToRight
    Set rng = Intersect(ws.UsedRange, ws.UsedRange.Offset(1, 0), ws.UsedRange.Offset(0, 1))

    i = 0
    For Each ditem In oDic
        i = i + 1
        For Each celrow In rng.Rows
            fndval = Application.Match(ditem, celrow, 0)
            If Not IsError(fndval) Then
                celrow.Cells(i) = ditem
            End If
        Next celrow
    Next ditem

    rng.Columns(oDic.Count + 1).Resize(1, rng.Columns.Count - oDic.Count).EntireColumn.Delete
    rng.Rows(1).Offset(-1).Resize(1, oDic.Count) = "Software"
    Set oDic = Nothing

    Application.ScreenUpdating = True

    End Sub

    Let me know how this works for you.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now