Home With VBA, how to sort data with different conditions?
Reply: 0

With VBA, how to sort data with different conditions?

user11666
1#
user11666 Published in September 19, 2018, 7:22 pm

I would really appreciate some help to find a correct approach to solve my issue.

I need to mach data from different sheets.

In the sheet 1, I have this list of data.

Key Reference      COL B          COL C      COL D
ID123                YZA              ...        ...
ID123                BBA              ...        ... 
ID123                XCP              ...        ... 
ID123                ABC
ID123                empty cell
ID123                …

ID124               empty cell
ID124               XCP

… …

In the sheet2, I will only have the list of unique references

ID123 ID124 ID125 ...

By unique reference, I need to sort the data from the column B with the below conditions:

  1. empty cells
  2. string "XCP"
  3. all the rest (from ABC to YZA)

Then, count the number of rows by unique reference Insert this number of rows in the sheet2 and paste the data sorted.

I think the easiest way to do it is to use a loop with a If statement for each of my condition instead of the sorting option.

The expected result are : So it seems to be the same as sheet 1 but col b respect my sorting conditions

Key Reference        COL B          COL C      COL D
ID123                empty cell       ...        ...
ID123                XCP              ...        ... 
ID123                ABC
ID123                YZA
ID123                …

ID124               empty cell
ID124               XCP

. Please see below the code i try to create

Sub mapbreak5() Dim lr As Long, r As Long lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row Dim rngKey As Range

For r = 2 To lr
If Sheets("Sheet1").Range("B" & r).Value = "" Then
'...
End If
Next r
'Or =>

Do
If Range("B2") Is Empty Then
Copy.EntireRow
    'find the respective key refence in the breaks sheet
    ThisWorkbook.Worksheets("breaks").Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        'check if the IDxx field is already populated
        If Range("F2") Is Empty Then
        Range("E2").Paste.Selection
        Else: ActiveCell.Offset (1)
        Rows.Select
        Selection.Insert Shift:=xlDown
        End If
    Else: ActiveCell.Offset (1)
    End If
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

Do
    If Range("B2") = "XCP" Then
    Copy.EntireRow
    'find the respective key refence in the breaks sheet
    ThisWorkbook.Worksheets("breaks").Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        'check if the IDxx field is already populated
        If Range("F2") Is Empty Then
        Range("E2").Paste.Selection
        Else: ActiveCell.Offset (1)
        Rows.Select
        Selection.Insert Shift:=xlDown
        End If
    Else: ActiveCell.Offset (1)
    End If
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

Do
    If Range("B2") Is Not Empty Or "XCP" Then
    Copy.EntireRow
    'find the respective key refence in the breaks sheet
    ThisWorkbook.Worksheets("breaks").Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        'check if the IDxx field is already populated
        If Range("F2") Is Empty Then
        Range("E2").Paste.Selection
        Else: ActiveCell.Offset (1)
        Rows.Select
        Selection.Insert Shift:=xlDown
        End If
    Else: ActiveCell.Offset (1)
    End If
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub
share|improve this question
  • What have you tried? What were the results? This is not a code-writing service, but we will be happy to help you debug your code. See stackoverflow.com/help for information as to how to ask a good question. – Ron Rosenfeld Aug 17 '14 at 18:32
  • Thanks Ron, I will see the help on how to ask a good question. I also updated my initial question with the piece of codes I tried. I will continue to try in this direction unless I am advise that it is not the best approach. – LK.3 Aug 17 '14 at 19:19
  • Record a macro while performing the task interactively, then inspect the macro. – Pieter Geerkens Aug 17 '14 at 19:30
  • @LK.3 It would be helpful if you could show what you expect for results, given the input. – Ron Rosenfeld Aug 17 '14 at 21:46
  • @RonRosenfeld I reply to your question by editing my initial question. thanks – LK.3 Aug 18 '14 at 9:02

2 Answers 2

active oldest votes
up vote 1 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.364116 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO