#1
|
|||
|
|||
Merging Rows - HELP please!
I am working on a table in Excel and the data needs to be on one line where currently 3 columns are in one row and the remaining 3 columns are in the next row. It currently looks like this (See attachment). I am stumped. How can I accomplish this without doing it line by line manually for multiple rows?
It seems simple but it's problematic. I have too many rows in this table to do this manually for each pair of rows that should be one row. TIA!! P.S. I'd also like to drop off the % in rows 2009/2010 for the entire table. . . Last edited by MessyJessy; 01-07-2015 at 10:57 PM. Reason: Added more detail to the question |
#2
|
||||
|
||||
Please post a sample sheet ( no pics) showing BEFORE and AFTER- Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Are they always like this with 2009/2010 in one row and 2011/2012 in the next? If it's more complicated than that, post your sheet. Pictures are nice but are less helpful.
|
#4
|
|||
|
|||
Full Table for merge rows help
I have a master table that is 4000+ rows. I created a subtable for Measure Code AM1_2 to share here. I created the master table by combining a separate table for each of the years represented in the column headings. When I did this I noticed that years 2009 and 2010 had named the measure codes with hyphens (AM1-2) and the remaining years had used the underline (AM1_2). The underline is the preferred naming convention so I corrected the 2009 and 2010 measure codes and the end result is as you see it in Book 2, attached. Now my job is to collapse the rows, or merge or join - whatever I need to do to get each measure on one line spanning all years. Not all measures span all years as you can see in my original screenshot for Measure code AM1_1. The problem is 2009/2010 versus the remaining years, in all Measure Codes, because of the original difference in naming the measures for those years.
I think there are going to be other problems going forward - I am looking at the percentages (%) in the 2009 and 2010 columns, wondering if that's why conditional formatting isn't working. But first things first - fixing the rows. I am an Excel newbie, unfortunately, and this seems to be a complicated project to begin with. Usually I can browse around and visit some tutorials and get acquainted with a program without being reliant on someone else coming to the rescue. Excel is going to be my exception. I appreciate every bit of help you can give. Thanks! Edit: I don't know what "before and after" would be referring to - this is the before, and I want to get to the after - unless you want to see the table before I corrected the measure names? Last edited by MessyJessy; 01-08-2015 at 10:30 AM. Reason: Regarding "before and after" |
#5
|
|||
|
|||
At first glance, this look really easy. All you need to do is select E2:H2, right click, select Delete..., and then Shift cells up.
Unfortunately, there is a single Provider Number, 381319, among the data you provided which only has one record so when you do the above, its data is shifted to the preceding Provider Number, 381318. See attached. Is that going to be a problem? If so, we can fix that. If not, then all you need to do now is delete all the rows with no data. That is easily done by a quick sort and delete. One thing I've noticed though, the data for 2009 and 2010 are percentages. The subsequent data are not. |
#6
|
|||
|
|||
Problem solved -
Thanks for looking at the table. There was a problem with text versus numbers mix in some of the columns. I've addressed that, turned text to #s and got rid of the %. With that, conditional formatting now works. It was problematic in that using shift up as suggested skewed the data - on the larger table there are more instances like the one you mention.
Last edited by MessyJessy; 01-09-2015 at 02:19 AM. Reason: a little more clarity |
#7
|
|||
|
|||
I added a blank row wherever there was an instance that needed that in order to keep the data from being skewed with shift up. I wanted a faster, easier fix, but happy to have it done.
|
#8
|
||||
|
||||
Try the following macro:
Code:
Sub Demo() Dim lRow As Long, lCol As Long, i As Long, j As Long, SBar As Boolean With Application SBar = .DisplayStatusBar .DisplayStatusBar = True .ScreenUpdating = False .Calculation = xlManual End With With ThisWorkbook.Worksheets("AMI_2").UsedRange lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row - 1 lCol = .Cells.SpecialCells(xlCellTypeLastCell).Column For i = lRow To 1 Step -1 Application.StatusBar = "Processing row " & i If .Cells(i, 2).Value = .Cells(i + 1, 2).Value Then For j = 3 To lCol If Len(Trim(.Cells(i, j).Value)) > 0 Then .Cells(i + 1, j).Value = .Cells(i, j).Text Exit For End If Next .Rows(i).EntireRow.Delete End If Next End With With Application .Calculation = xlAutomatic .StatusBar = False .DisplayStatusBar = SBar .ScreenUpdating = True End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
join, merge, rows |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete blank rows between the two rows that contain data | beginner | Excel Programming | 5 | 12-26-2014 12:29 AM |
Delete All empty Rows - Print - Undo all Rows deleted | Bathroth | Word VBA | 1 | 10-01-2014 01:40 PM |
Grouping table rows to prevent individual rows from breaking across pages | dennist77 | Word | 1 | 10-29-2013 11:39 PM |
Count rows and add blank rows accordingly | Hoochtheseal | Word VBA | 1 | 01-29-2013 09:23 PM |
merging rows and creating sub-rows | gib65 | Excel | 2 | 12-09-2011 02:09 PM |