Excel Macro or VBA

[Guest]

I have one excel sheet1 look like the following:

UserName Location Division Software Name Version

Maria.Sigmu Parkade Probation Adobe Reader 7.0
Maria.Sigmu Parkade Probation Lotus Notes 6.5.5
Eric.Bell Parkade IT WebFldrs 1.0
Eric.Bell Parkade IT Adobe Reader 7.0
Eric.Bell Parkade IT Symantec 8.1

I want to create sheet 2 using Excel Macro in the following
format:

UserName Location Division Software Name Version

Maria.Sigmu Parkade Probation Adobe Reader 7.0
Lotus Notes 6.55
Eric.Bell Parkade IT WebFldrs 1.0
Adobe Reader 7.0
Symantec 8.1

I am a novice user in Excel VBA. I greatly appreciate any help.

 

[Dave Peterson]

Maybe you could do something different.

Keep the data, but hide it--if the value of the cell is the same as the one
right above, make the font color match the fill color (white on white).

It might make it easier to other things (like data|filter|Autofilter and
sorting).

If you want to try, take a look at Debra Dalgleish's site:
http://contextures.com/xlCondFormat03.html#Duplicate

 

[Ken]

thadi

It looks like you want to have the name, location and division ony show
up the on the first row of each individual. To replace the names
(assumed to be in column A) with a blank other than the first
occurence, insert a column B and put in the formula

=if(a2=a1,"",a2)

and copy it down all the rows of relevant data. That will replace
everything other than the first occurence of each name with a blank.
Convert that to values (copy the column then,
edit-paste-special-values, on itself). You can repeat for location and
division columns, or you can sort by column B (after converting to
values), delete the location and division for all the rows that are
blank in column B, then resort by column A. I would recomment that you
keep the intact column A (with the username on each row) on the
spreadsheet somewhere. It could be outside the print range or hidden,
if you really don't want to see it, but, in case you ever want to sort
the data by location, division, software, or version. Without it, you
will probably have trouble getting the data back in order by username
after sorting once you have deleted the username, division, and
location from most of the rows.

Good luck.

Ken
Norfolk, Va

 

[Ken]

Dave
I never used conditional formatting for that before. I like it better
than what I usually do.
Thanks
Ken

 

[gimme_this_gimme_that]

What you want is a standard pivot table. Look up Pivot table from the
menus and experiment. You'll get it.

 

[Guest]

Thank you all for the replies. Those are all good solutions. But the
spreadsheet will keep getting bigger every week, and I like to run a macro
which will do the job in one shot. Is that possible?
Thanks

 

[Jon Peltier]

How about something like this:

Sub ClearRoutine()
Dim theRange As Range
Dim cCell As Range, nRow As Double, nCol As Double

Set theRange = ActiveSheet.UsedRange

' Start from the bottom row and move upward
For nRow = theRange.Rows.count To 2 Step -1

' Ignore cells in top row of selection
For nCol = 1 To theRange.Columns.count

' Don't bother if it's already blank
If Len(theRange.Cells(nRow, nCol).Value) > 0 Then
If theRange.Cells(nRow, nCol).Value = theRange.Cells(nRow - 1,
nCol).Value Then
' If it matches the cell above, then clear it
theRange.Cells(nRow, nCol).Value = ""
End If
End If

Next nCol

Next nRow
End Sub


- Jon

 

[Guest]

Thank you, it's working.
--
thadi

How about something like this:

Sub ClearRoutine()
Dim theRange As Range
Dim cCell As Range, nRow As Double, nCol As Double

Set theRange = ActiveSheet.UsedRange

' Start from the bottom row and move upward
For nRow = theRange.Rows.count To 2 Step -1

' Ignore cells in top row of selection
For nCol = 1 To theRange.Columns.count

' Don't bother if it's already blank
If Len(theRange.Cells(nRow, nCol).Value) > 0 Then
If theRange.Cells(nRow, nCol).Value = theRange.Cells(nRow - 1,
nCol).Value Then
' If it matches the cell above, then clear it
theRange.Cells(nRow, nCol).Value = ""
End If
End If

Next nCol

Next nRow
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______