Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
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. -- thadi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
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 Tasmania wrote: 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. -- thadi -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
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 On Jan 26, 3:08 pm, Tasmania wrote: 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. -- thadi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
Dave
I never used conditional formatting for that before. I like it better than what I usually do. Thanks Ken On Jan 26, 4:02 pm, Dave Peterson wrote: 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 Tasmania wrote: 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. -- thadi-- Dave Peterson- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
What you want is a standard pivot table. Look up Pivot table from the
menus and experiment. You'll get it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
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 -- thadi " wrote: What you want is a standard pivot table. Look up Pivot table from the menus and experiment. You'll get it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
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 _______ "Tasmania" wrote in message ... 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. -- thadi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro or VBA
Thank you, it's working.
-- thadi "Jon Peltier" wrote: 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 _______ "Tasmania" wrote in message ... 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. -- thadi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Call macro stored in Excel workbook from Outlook's macro | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |