Another Related Question

Posted on 2016-09-06
Medium Priority
Last Modified: 2016-09-27
I had this question after viewing EXCEL Match data rows in excel.  (not same amount of values).

I have data in column E that needs to stay tied to Column D and/or Column C.

Any suggestion to include that in the MACRO you wrote?
Question by:Julie Lyman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 26

Expert Comment

ID: 41786322
yes, use the code below and it will not touch your column E.  

Option Explicit

Sub Test()
Dim i As Long, lastrow As Long
Application.ScreenUpdating = False
 lastrow = Range("C" & Rows.Count).End(xlUp).Row

    Columns("A:E").Sort Key1:=Range("A2"), order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Columns("C:E").Sort Key1:=Range("C2"), order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    i = 2 '  if your data has header. if not change to 1
        If Cells(i, "A") > Cells(i, "C") And Cells(i, "C") > "" Then
            Cells(i, "A").Resize(1, 2).Insert xlShiftDown
        ElseIf Cells(i, "A") < Cells(i, "C") And Cells(i, "A") > "" Then
            Cells(i, "C").Resize(1, 2).Insert xlShiftDown
        End If
        i = i + 1
    Loop Until Cells(i, "A") = "" And Cells(i, "C") = ""
Application.ScreenUpdating = True
End Sub

Open in new window


Author Comment

by:Julie Lyman
ID: 41787100
I tried this and I actually want Column D and E to stay "connected"  

Before the Macro is Run on the same row Sample Data is:  
Sample of Column D Data might be:  449303 OIL CLOTH MFG                        
Sample of Column E Data Might be:  <option value="449303">OIL CLOTH MFG                      </option>

I want the same matching data in Column D and E, however the result will be on a different Row based on what you wrote in the original MACRO.
LVL 26

Expert Comment

ID: 41787279
Can you depict this with an example as you did in your previous question . Two tables the current and the one aftermath
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Julie Lyman
ID: 41787675
I have an attached sample of Data
Sheet:  PreMacro
Sheet:  Post Macro

In the Post Macro you will see that I have inserted 2 columns A and B

The data we originally discussed as sample data was columns A-D,  and has been moved to Columns C-F

Both Column A and B are using Formulas, Post Macro is the desired result.

The only thing I need currently is for Column G to move with Column F, when your macro is run.

Note:  that there is a few manual changes I have to make in order to match the Description Columns D and F.  You can see that code 1803 as an example.  I am manually matching the data in column D with Column F.
LVL 26

Accepted Solution

ProfessorJimJam earned 2000 total points (awarded by participants)
ID: 41789239
Hi, i just looked at your sample data uploaded, it contradicts with the description you are giving.

the sheet pre macro has 5 columns with data and your post macro sheet has 7. how these can correlate, it  cannot.  

the question you asked was to align rows.

i have attached a solution to the example you previously provided and there i added addition column and when you run the macro, the addtional column of E will not be affected and will move along the column C and D
LVL 26

Expert Comment

ID: 41817522

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question