Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2015, 10:50 PM
MessyJessy MessyJessy is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 32bit
Novice
Merging Rows - HELP please!
 
Join Date: Jan 2015
Posts: 4
MessyJessy is on a distinguished road
Default 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. . .
Attached Images
File Type: jpg ExcelRowsProblem.jpg (42.9 KB, 20 views)

Last edited by MessyJessy; 01-07-2015 at 10:57 PM. Reason: Added more detail to the question
Reply With Quote
  #2  
Old 01-08-2015, 02:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 01-08-2015, 08:35 AM
gebobs gebobs is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 01-08-2015, 10:28 AM
MessyJessy MessyJessy is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 32bit
Novice
Merging Rows - HELP please!
 
Join Date: Jan 2015
Posts: 4
MessyJessy is on a distinguished road
Default 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?
Attached Files
File Type: xlsx Book2.xlsx (15.2 KB, 9 views)

Last edited by MessyJessy; 01-08-2015 at 10:30 AM. Reason: Regarding "before and after"
Reply With Quote
  #5  
Old 01-08-2015, 11:05 AM
gebobs gebobs is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx Book2.xlsx (12.5 KB, 7 views)
Reply With Quote
  #6  
Old 01-09-2015, 02:08 AM
MessyJessy MessyJessy is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 32bit
Novice
Merging Rows - HELP please!
 
Join Date: Jan 2015
Posts: 4
MessyJessy is on a distinguished road
Default 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
Reply With Quote
  #7  
Old 01-09-2015, 02:21 AM
MessyJessy MessyJessy is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 32bit
Novice
Merging Rows - HELP please!
 
Join Date: Jan 2015
Posts: 4
MessyJessy is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 01-14-2015, 01:38 AM
macropod's Avatar
macropod macropod is offline Merging Rows - HELP please! Windows 7 64bit Merging Rows - HELP please! Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm. Although the instructions are for Word, the Excel procedure is basically the same. Do note that macros can't be saved in xlsx files - you can run them in an xlsx file, however. If you want to keep the macro in the file for future use, save the file in the xls or xlsm format - or add the macro to your personal.xlsm workbook (see http://www.rondebruin.nl/win/personal.htm).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
join, merge, rows

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging Rows - HELP please! Delete blank rows between the two rows that contain data beginner Excel Programming 5 12-26-2014 12:29 AM
Merging Rows - HELP please! Delete All empty Rows - Print - Undo all Rows deleted Bathroth Word VBA 1 10-01-2014 01:40 PM
Merging Rows - HELP please! Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Merging Rows - HELP please! Count rows and add blank rows accordingly Hoochtheseal Word VBA 1 01-29-2013 09:23 PM
Merging Rows - HELP please! merging rows and creating sub-rows gib65 Excel 2 12-09-2011 02:09 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft