Cutting and Pasting Rows Based on a Matching Criteria

LukeAJN

New Member
Joined
Feb 7, 2022
Messages
11
Hi All

I have a particular problem here that I am wondering if anyone can figure out the best way to go about it. I currently have a spreadsheet with a jumbled and out of order list of values in a certain column. I need to be able to search the spreadsheet, find the two corresponding entries, and then cut and paste them so that they are in rows next to each other. The data is already sorted largest to smallest so when cutting a row it must always best inserted below the corresponding entries.


A small visualization of this would be like the below




COL B
X1
Y2
Z1
Y1
Z2
X2

This would then become


COL B
X1
X2
Y2
Y1
Z1
X2
 
The values are not always paired together , I was showing the values paired together as an example of what the final output needs to look like


A small visualization of this would be like the below




COL B
X1
Y2
Z1
Y1
Z2
X2

This would then become


COL B
X1
X2
Y2
Y1
Z1
Z2
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
The values are not always paired together , I was showing the values paired together as an example of what the final output needs to look like
do you have something like this ... e.g. A and Z. B and Y, C and X are always paired, but sometimes you will have records with the same pairing, but in reverse order Z and A, Y and B, X and C.
You want them listed together regardless of which number in the pair occurs first

So a list that looks like this

Book1
AB
1AZ
2AZ
3BY
4BY
5CX
6YB
7AZ
8ZA
9XC
10AZ
Sheet2


will end up looking like this
Book1
AB
1AZ
2AZ
3AZ
4AZ
5ZA
6BY
7BY
8YB
9CX
10XC
Sheet2
 
Upvote 0
Hi Bosquedeguate, first of all thank you for trying to help. I think we have started to confuse each other at this stage lol. Perhaps showing the actual data might make it clearer..

This is how to data looks like right now, you can see there are three columns, an account number, the currency of that account, and the amount held in the account. I have taken the liberty of shading in the accounts that are mapped to each other. For example, 10072023 and 4320018304 are linked together and will always be linked together. Because these are linked together I need the smaller value to be cut and insert below the larger value, for all these accounts.

1691673762633.png


This would result in a finished output such as the below

1691673981571.png
 
Upvote 0
Do you have a list (maybe on a different worksheet) that defines the relationships?

Like the A and Z example I posted?

If not, could you create one?

So one of the numbers is the primary number, right?
 
Upvote 0
No there is nothing that defines the relationship unfortunately, which means each account number needs to be hardcoded into the VBA. It is almost like I need the code to do the below, and then I would need to repeat this for every pair

If 10072023 Is Above 4320018304 Then
Paste Row Containing 4320018304 below Row Containing 10072023 Else
Paste Row Containing 4320018304 above Row Containing 10072023
 
Upvote 0
Hi Bosquedeguate, first of all thank you for trying to help. I think we have started to confuse each other at this stage lol. Perhaps showing the actual data might make it clearer..

This is how to data looks like right now, you can see there are three columns, an account number, the currency of that account, and the amount held in the account. I have taken the liberty of shading in the accounts that are mapped to each other. For example, 10072023 and 4320018304 are linked together and will always be linked together. Because these are linked together I need the smaller value to be cut and insert below the larger value, for all these accounts.

View attachment 9

This would result in a finished output such as the below

View attachment 96944
I do not think hardcoding the links between the two numbers should be hardcoded.
I think a better way to do this is to create a list of the two linked values
Book1
AB
1432001830410072023
243200173924370042912
3437001839810006247
4432000580710001955
5
Sheet2


This would be much better than hardcoding the values in VBA code. With the table you could use some form of a LOOKUP function or MATCH and INDEX function to create your algorithm for
putting (sorting) linked values together. How many pairs of values are we talking about? Anyone helping you with the VBA code will need to know all of these linked values. Do you have VBA now that defines these linked values?
 
Upvote 0
Hi Bosquedeguate, no there is no VBA code as of yet that defines these linked values. In total there are 84 different account numbers which means there needs to be 42 links/pairs
 
Upvote 0
Hi Bosquedeguate, no there is no VBA code as of yet that defines these linked values. In total there are 84 different account numbers which means there needs to be 42 links/pairs
OK ... can you provide a mini-sheet of 20-30 records that I can work with? By the way ... one of your attachment was not valid or did not work i.e. View attachment 9 in your previous reply.
 
Upvote 0
Hi, I am actually on my work PC right now so can't download the package that will allow a mini sheet upload. I will have to wait until I am home this evening before giving it a try.
 
Upvote 0
I think I have a working solution - here are the steps
1) Sorts the rows by the amount largest to smallest
2) Moves all the Primary account numbers to the top of the list
3) Moves (pairs) the Secondary account number with Primary account number
4) Dependent upon creating a paired list (another sheet) matching Primary and Secondary Account numbers

Please provide a larger data set for testing purposes.

Before (after sorting)
OrderLinkedValues.xlsm
ABC
1Account NumberCCYAmount
210072023EUR1,242,721,331.69
34320017392EUR444,744,495.06
44320018304EUR430,654,789.01
54370018398JPY63,232,200.00
64320005807USD47,134,293.89
74370042912EUR42,356,879.06
810001955USD39,204,045.00
910006247JPY36,133,093.89
Sheet1


After (after pairing)
OrderLinkedValues.xlsm
ABC
1Account NumberCCYAmount
24320017392EUR444,744,495.06
34370042912EUR42,356,879.06
44320018304EUR430,654,789.01
510072023EUR1,242,721,331.69
64370018398JPY63,232,200.00
710006247JPY36,133,093.89
84320005807USD47,134,293.89
910001955USD39,204,045.00
Sheet1


It is dependent on creating a pair list (I have it on another sheet name "Linked_Accounts")
OrderLinkedValues.xlsm
AB
1Primary AcctSecondary Acct
2432001830410072023
343200173924370042912
4437001839810006247
5432000580710001955
Linked_Accounts


Here is the first version of the VBA code
VBA Code:
Option Explicit
Sub GroupLinkedAccounts()
'
' Group Linked Accounts
'
  Dim lrSrc As Long, lrLA As Long
  Dim r As Long
  Dim rPrimary As Long, rSecondary As Long
  Dim rLastPrimary As Long
  Dim rng As Range, rngLA As Range
  Dim rngLA2 As Range
  Dim rngprev As Range
  Dim rngSrc As Range
  Dim wksLA As Worksheet 'Linked Accounts worksheet
  Dim wksSrc As Worksheet
  Dim Acct1, idx
  Dim rPrevIsPrimary As Long, rIsPrimary As Long

  
  Set wksSrc = ActiveSheet
  Set wksLA = Worksheets("Linked_Accounts")
  lrSrc = wksSrc.Range("A" & Rows.Count).End(xlUp).Row
  lrLA = wksLA.Range("A" & Rows.Count).End(xlUp).Row
  Set rngLA = wksLA.Range("A2:A" & lrLA)
  Set rngLA2 = wksLA.Range("B2:B" & lrLA)
'
  SortAmountMaxToMin 'separate macro/sub

  rPrimary = 1
  rLastPrimary = 1
  rSecondary = 2
  For r = 3 To lrSrc
    Range("A" & r & ":C" & r).Select
    'Is the value in Row r a Primary account number
    'find account number in linked account list
'    On Error GoTo end_of_loop_1
    On Error Resume Next
    With WorksheetFunction
      Set rngprev = wksSrc.Range("A" & r - 1 & ":C" & r - 1)
      rPrevIsPrimary = 0
      rPrevIsPrimary = .Match(rngprev(1), rngLA, 0)
      rIsPrimary = 0
      rIsPrimary = .Match(Selection(1), rngLA, 0)
      If rIsPrimary > 0 Then rLastPrimary = r
    End With
    If Not rPrevIsPrimary And rIsPrimary Then
      rPrimary = rPrimary + 1
      Selection.Cut
      wksSrc.Range("A" & rPrimary & ":C" & rPrimary).Select
      Selection.Insert Shift:=xlDown
    End If
end_of_loop_1:
    'Debug.Print Err.Number, Err.Description
  Next r
  
  'Now match up Primary and Secondary Accounts
  'All the primary accounts are at the top of the list
  
  'rPrimary - 1 (minus one) should be the last Primary record in the list
  'if there are no primary accounts in the list exit
  If rLastPrimary = 0 Then Exit Sub
  
  Set rngSrc = wksSrc.Range("A2:A" & lrSrc)

  For r = rLastPrimary To lrSrc
    Range("A" & r & ":C" & r).Select
    On Error Resume Next
    With WorksheetFunction
      idx = 0
      Acct1 = 0
      idx = .Match(Selection(1), rngLA2, 0)
      If idx > 0 Then
        Acct1 = .Index(rngLA, idx)
      End If
    End With
    If Acct1 > 0 Then
      rPrimary = 0
      rPrimary = WorksheetFunction.Match(Acct1, rngSrc, 0) + 1
      If rPrimary > 0 Then
        rSecondary = rPrimary + 1
        Selection.Cut
        wksSrc.Range("A" & rSecondary & ":C" & rSecondary).Select
        Selection.Insert Shift:=xlDown
      End If
    End If
  Next r
End Sub

Sub SortAmountMaxToMin()
'
' SortAmountMaxToMin
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C9"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C9")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top