Excel formula needed to copy data

[maltait]

Hi all,

Hoping you can help as have an excel sheet with over 100,000 lines.

I need 2 things :

See pic formula 1
First a formula that compares data in column b the previous line to the next and if different it copies the whole line to another spreadsheet to create a new list with out the duplicates. Line 1 may be repeated in line 10,000 which is ok as I need to capture all instances just not when they are straight after each other (although there may be an instance where this occurs but I don't have any other data to distinguish the difference)

So would look something like in pic formula 1 new

Secondly, I need a separate formula for another spreadsheet that reads the word "code" and copies that line then looks for the word "all clear" and copies that line. Then repeats. This is repeated throughout the document. See pic formula 2.

So I need to capture every instance copy this to create a new list (See pic formula 2 new)

Hope that all makes sense

Thank you for any help you can provide

Cheers
Mal

 

[RobP]

Hi Mal,
you need to update your profile with which Excel version you are using, as different versions offer certain formula alternatives.
cheers
Rob

 

[maltait]

Hi Mal,
you need to update your profile with which Excel version you are using, as different versions offer certain formula alternatives.
cheers
Rob

Hey Rob,
Thanks for that its the 2016 version so have updated now thanks for letting me know
Cheers
Mal

 

[RobP]

Hi Mal,

not sure theres a magic formula for that - but it can be done with a VBA macro as such.

There are 2 macros for (1 for each of your problems), and I have them inside a module within my workbook, as I have utilised Sheet1,2,3,4 (1&2 for the first problem, 3&4 for the second). You might have them in different workbooks / sheet names & columns, so might need a little tweaking.

First MAcro here, finds your unique rows in problem 1. examples of my data layout, and result below it.

Sub unique_search()

Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim last_row, new_row, count As Long
Dim data_array, unique_array As Variant

Set Ws1 = Sheet1
Set Ws2 = Sheet2

last_row = Ws1.Cells(Rows.count, 2).End(xlUp).Row  'assumes test "met call -x.x bed x" is in col B (col2)
new_row = 1
data_array = Ws1.Range(Cells(1, 1), Cells(last_row, 2)).Value 'assumes data starts in cell A1, ends in Bxxx, on "Sheet1"
ReDim unique_array(1 To last_row, 1 To 2)

'scan data for unique rows
For count = 1 To last_row

    'check for last_row, as cannot compare with last_row+1. so skip out
    If count = last_row Then Exit For
    
    'check ColB row <> next row ColB
    If data_array(count, 2) <> data_array(count + 1, 2) Then
        unique_array(new_row, 1) = data_array(count, 1)
        unique_array(new_row, 2) = data_array(count, 2)
        new_row = new_row + 1 'next row for unique_array
    End If

Next count

'add last row of data as its always needed
unique_array(new_row, 1) = data_array(count, 1)
unique_array(new_row, 2) = data_array(count, 2)

'store unique list on "Sheet2" in Col A & B starting Row 1
Ws2.Range("A1:B" & UBound(unique_array, 1)).Value = unique_array

MsgBox ("All Complete")

End Sub

Book1
	A	B
1	line 1494: 2022/12/31 sat 12:13:44:50	met call - 5.1 bed 12
2	line 1494: 2022/12/31 sat 12:13:44:62	met call - 5.1 bed 12
3	line 1494: 2022/12/31 sat 12:13:44:67	met call - 5.1 bed 12
4	line 1494: 2022/12/31 sat 12:13:44:73	met call - 5.1 bed 12
5	line 1494: 2022/12/31 sat 12:13:44:73	met call - 5.1 bed 12
6	line 1494: 2022/12/31 sat 16:47:22:81	met call - 8.2 bed 5
7	line 1494: 2022/12/31 sat 16:47:22:06	met call - 8.2 bed 5
8	line 1494: 2022/12/31 sat 16:47:22:51	met call - 8.2 bed 5
9	line 1494: 2022/12/31 sat 16:47:22:73	met call - 8.2 bed 5
10	line 1494: 2022/12/31 sat 16:47:22:65	met call - 8.2 bed 5
11	line 1494: 2022/12/31 sat 16:47:22:02	met call - 8.2 bed 5
12	line 1494: 2022/12/31 sat 18:47:22:06	met call - 5.1 bed 18
13	line 1494: 2022/12/31 sat 18:47:22:06	met call - 5.1 bed 12
Sheet1

Book1
	A	B
1	line 1494: 2022/12/31 sat 12:13:44:73	met call - 5.1 bed 12
2	line 1494: 2022/12/31 sat 16:47:22:02	met call - 8.2 bed 5
3	line 1494: 2022/12/31 sat 18:47:22:06	met call - 5.1 bed 18
4	line 1494: 2022/12/31 sat 18:47:22:06	met call - 5.1 bed 12
Sheet2

Second Macro here handles your second problem:

Sub code_search()

Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim last_row, new_row, countA, countB As Long
Dim data_array, unique_array As Variant

Set Ws1 = Sheet3
Set Ws2 = Sheet4

last_row = Ws1.Cells(Rows.count, 2).End(xlUp).Row  'assumes data is in col B (col2)
new_row = 1
ReDim data_array(1 To last_row, 1 To 2)
ReDim unique_array(1 To last_row, 1 To 2)

data_array = Sheets("Sheet3").Range(Cells(1, 1), Cells(last_row, 2)).Value 'assumes data starts in cell A1, ends in Bxxx, on "Sheet3"
'scan data for unique rows
For countA = 1 To last_row

    'check for last_row, as cannot compare with last_row+1. so skip out
    If countA = last_row Then Exit For
    
    If Left(LTrim(data_array(countA, 2)), 4) = "code" Then '"code" found
        unique_array(new_row, 1) = data_array(countA, 1)
        unique_array(new_row, 2) = data_array(countA, 2)
        new_row = new_row + 1 'next row for unique_array
        
        For countB = countA + 1 To last_row
            If Left(LTrim(data_array(countB, 2)), 9) = "all clear" Then
                unique_array(new_row, 1) = data_array(countB, 1)
                unique_array(new_row, 2) = data_array(countB, 2)
                new_row = new_row + 1 'next row for unique_array
                countA = countB 'realign first count
                Exit For 'jump out of countB loop if "all clear" found
            End If
        Next countB
    End If

Next countA


'store unique list on "Sheet4" in Col A & B starting Row 1
Ws2.Range("A1:B" & UBound(unique_array, 1)).Value = unique_array

MsgBox ("All Complete")

End Sub

Book1
	A	B
1	line 1494: 2022/12/31 sat 12:13:44:50	code blue adult - Bed 8
2	line 1494: 2022/12/31 sat 12:13:44:62	code blue adult - Bed 8
3	line 1494: 2022/12/31 sat 12:13:44:67	code blue adult - Bed 8
4	line 1494: 2022/12/31 sat 12:13:44:73	code blue adult - Bed 8
5	line 1494: 2022/12/31 sat 16:47:22:81	all clear code blue adult - Bed 8
6	line 1494: 2022/12/31 sat 16:47:22:06	all clear code blue adult - Bed 8
7	line 1494: 2022/12/31 sat 16:47:22:51	all clear code blue adult - Bed 8
8	line 1494: 2022/12/31 sat 16:47:22:73	all clear code blue adult - Bed 8
9	line 1494: 2022/12/31 sat 16:47:22:65	code blue adult - Bed 5
10	line 1494: 2022/12/31 sat 16:47:22:02	code blue adult - Bed 5
11	line 1494: 2022/12/31 sat 18:47:22:06	all clear code blue adult - Bed 5
12	line 1494: 2022/12/31 sat 18:47:22:06	all clear code blue adult - Bed 5
Sheet3

Book1
	A	B
1	line 1494: 2022/12/31 sat 12:13:44:50	code blue adult - Bed 8
2	line 1494: 2022/12/31 sat 16:47:22:81	all clear code blue adult - Bed 8
3	line 1494: 2022/12/31 sat 16:47:22:65	code blue adult - Bed 5
4	line 1494: 2022/12/31 sat 18:47:22:06	all clear code blue adult - Bed 5
Sheet4

Let us know how you get on.
cheers
Rob

 

[maltait]

Hi Rob,

Will have a look now at the above, thank you greatly. Will report back once I load it up

Thanks
Mal