Excel sheet sorting

Posted on 2007-08-01
Medium Priority
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 81

Expert Comment

ID: 19614309
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

ID: 19614317

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


Expert Comment

ID: 19614402
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Expert Comment

ID: 19614426
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 81

Expert Comment

ID: 19614543
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

ID: 19614582
'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

jeverist earned 2000 total points
ID: 19621520
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 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