Solved

Another Related Question

Posted on 2016-09-06
6
40 Views
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?
0
Comment
Question by:Julie Lyman
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
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
    Do
        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

0
 

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.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41787279
Can you depict this with an example as you did in your previous question . Two tables the current and the one aftermath
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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.
DataSample.xlsm
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 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
EE.xlsm
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41817522
.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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