Page 2 of 2 FirstFirst 1 2
Results 11 to 15 of 15

Thread: help with data project

  1. #11
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    47
    Articles
    0
    Excel Version
    2016 32bit


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Sorry, missed your last post.
    Something went wrong with the pasting of the merged macros. Change the last part of it this way:
    Code:
    [...]
                With .Sort
                    .SetRange rngSort
                    .Header = xlYes
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
            Next i
            'mark if missing numbers
            For i = 3 To lr
                If .Range("A" & i).Value - .Range("A" & i - 1).Value > 1 Then .Range("B" & i).Value = "#"
            Next i
        End With
        Application.ScreenUpdating = True
        
    End Sub
    Last edited by rollis13; 2021-03-04 at 11:20 PM.

  2. #12
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    47
    Articles
    0
    Excel Version
    2016 32bit
    Sorry again, it's still not right, this should be it:
    Code:
    [...]
            'extract numbers to column A
            For i = 2 To lr
                With .Range("C" & i)
                    SearchString = .Value
                    SearchChar = "."
                    MyPos = InStr(1, SearchString, SearchChar, 1)
                    MyStr = Left(SearchString, MyPos - 1)
                    .Offset(0, -2).Value = MyStr
                End With
            Next i
            'sort on column A
            .Sort.SortFields.Clear
            .Sort.SortFields.Add2 Key:=Range("A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            Set rngSort = .Range("A1:C" & lr)
            With .Sort
                .SetRange rngSort
                .Header = xlYes
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            'mark if missing numbers
            For i = 3 To lr
                If .Range("A" & i).Value - .Range("A" & i - 1).Value > 1 Then .Range("B" & i).Value = "#"
            Next i
        End With
        Application.ScreenUpdating = True
        
    End Sub

  3. #13
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    49
    Articles
    0
    Excel Version
    Office 365
    Thank you.

    Far more effort than expected . . .

  4. #14
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    49
    Articles
    0
    Excel Version
    Office 365
    I was getting confused by the number of files + missing songs not adding up to number of songs . . . Then I realized that sometimes there is more than one song in a row missing, so I changed the output to the B cell to be

    Code:
    (.Range("A" & i).Value - .Range("A" & i - 1).Value) - 1
    so that the total of the B column + (A - 1) = number of songs.

    Not a big deal . . .

  5. #15
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    47
    Articles
    0
    Excel Version
    2016 32bit
    That's ok, the first intent was only to mark the presence of missing numbers.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •