Solved

# Excel sheet sorting

Posted on 2007-08-01
Medium Priority
247 Views
Hi,

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.

Regards
Sharath
0
Question by:bsharath

LVL 81

Expert Comment

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

0

LVL 11

Author Comment

ID: 19614317
byundt

Any way with a macro.

If not then i shall say what i have understood
At the end of each row
=IF(Sheet1!A2="","",Sheet1!A2)
On the next row this
=IF(SUMPRODUCT((Sheet1!\$A\$2:\$A\$1000=\$A2)*(Sheet1!\$B\$2:\$Z\$1000=B\$1))>0,B\$1,"")

0

LVL 3

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.
0

LVL 3

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
0

LVL 81

Expert Comment

ID: 19614543
Sharath,
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.

0

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
Next
Next
End Sub
0

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
End If
Next

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.

Jim
0

## Featured Post

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.
###### Suggested Courses
Course of the Month16 days, 14 hours left to enroll