Cleaning a Spreadsheer

[Prime88]

Afternoon!

This is probably very simple, but I have a large spreadsheet ( 100,000 records ) and I need to remove about 3000 that I have on 2 x different excel sheets..

Without me searching for every single record and removing them, is there a way I can do this quicker?

Thanks in advance!!

 

[duggie33]

Morning!

How do you know which 3000 rows need to be removed?

Doug

 

[Prime88]

Morning!

How do you know which 3000 rows need to be removed?

Doug

All of them on the smaller spreadsheets, so I have 2 spreadsheets with records on them, and wondered if there is a quicker way to identify them on the large spreadsheet and remove

 

[duggie33]

So if the record is on the smaller and larger spreadsheet, you want to remove it from the larger spreadsheet? If yes, on the larger spreadsheet, you would add a column that identifies the matches, sort them so they are all together, highlight them, and then delete them.

If you could please provide some sample data, I can write the match formula for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. As of October 2023, the latest version is 2.1. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Doug

 

[duggie33]

I made up some data...

Book1
	A	B	C	D	E
1	A	B	C	D	On Sheet 2?
2	A2	B2	C2	D2	TRUE
3	A3	B3	C3	D3	TRUE
4	A4	B4	C4	D4	FALSE
5	A5	B5	C5	D5	TRUE
6	A6	B6	C6	D6	FALSE
7	A7	B7	C7	D7	FALSE
8	A8	B8	C8	D8	FALSE
9	A9	B9	C9	D9	FALSE
10	A10	B10	C10	D10	FALSE
11	A11	B11	C11	D11	FALSE
12	A12	B12	C12	D12	FALSE
13	A13	B13	C13	D13	FALSE
14	A14	B14	C14	D14	TRUE
15	A15	B15	C15	D15	TRUE
16	A16	B16	C16	D16	TRUE
17	A17	B17	C17	D17	FALSE
18	A18	B18	C18	D18	TRUE
19	A19	B19	C19	D19	TRUE
20	A20	B20	C20	D20	FALSE
21	A21	B21	C21	D21	TRUE
22	A22	B22	C22	D22	FALSE
23	A23	B23	C23	D23	TRUE
24	A24	B24	C24	D24	TRUE
25	A25	B25	C25	D25	FALSE
26	A26	B26	C26	D26	TRUE
Sheet1

Cell Formulas
Range	Formula
E2:E26	E2	=ISNUMBER(MATCH(BYROW(A2:D26,LAMBDA(y,TEXTJOIN("|",TRUE,y))),BYROW(Sheet2!$A$2:$D$13,LAMBDA(x,TEXTJOIN("|",TRUE,x))),0))
Dynamic array formulas.

Book1
	A	B	C	D
1	A	B	C	D
2	A2	B2	C2	D2
3	A3	B3	C3	D3
4	A5	B5	C5	D5
5	A14	B14	C14	D14
6	A15	B15	C15	D15
7	A16	B16	C16	D16
8	A18	B18	C18	D18
9	A19	B19	C19	D19
10	A21	B21	C21	D21
11	A23	B23	C23	D23
12	A24	B24	C24	D24
13	A26	B26	C26	D26
Sheet2

The TRUE/FALSE values could be sorted and then all the TRUE values could be highlighted and deleted. You will likely have to convert the formulas to values (Copy, Paste Special, Values) in order to sort because dynamic array formulas do not allow you to sort them.

Hope that helps,

Doug