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 |
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 |
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 |
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 - |
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. |
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. |
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 |
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 |
All times are GMT +1. The time now is 11:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com