remove data from a work sheet

Copper Contributor

I have two worksheets A and B.  worksheet B is a complete subset of worksheet A.   I want to remove all the data of work sheet B from worksheet A forming a new worksheet C.    Example:  I have a list of eligible voters.   I have data  from the county clerk that some of the eligible voters have voted.  I want to remove the voters who have already voted from the the list of all vo

5 Replies

@Terry Armentrout I'm inclined to suggest that you use Power Query for such a task. Connect to both tables A and B and than merge them with a so-called "left anti-join" that will produce a new table containing records that only exist in the first table.

 

The traditional Excel method to achieve the same would be to expand table A with a column that looks up (VLOOKUP or XLOOKUP) if the voter exists in table B. Then filter out the the ones that do and copy/paste the ones that don't to a new table.

@Terry Armentrout 

The key element of the calculation is to determine whether each person on the register has voted (once) or not.

= COUNTIFS(voted, register)

The list you require is then produced by filtering the register.

= LET(
    voted?, COUNTIFS(voted, register),
    FILTER(register, NOT(voted?))
  )

It is also possible to achieve the result in legacy spreadsheets by filtering a record index and then using SMALL to compact the list before returning the names.

@Riny_van_Eekelen 

The antijoin was made for this!   I find it difficult to determine where the border lies between PQ and DA; the overlap in functionality is so large.  Perhaps PQ if

1. You are using an old copy of Excel (2010-2019)

2. The 'voted' list is imported from an external source

3. The tables are large (perhaps, votes cast >> 10,000?)

4. Dynamic updating of the 'not voted' table isn't needed

Do you have advice to offer on the subject?

 

@Peter Bartholomew

I've only been playing around with PQ for just over a year now and have become very fond of it. It's simplicity in completing quite complicated tasks is amazing. In contrast with, for instance, VBA that often requires quite complex coding to achieve relatively simple tasks.

 

Not needing to load the data into the workbook you happen to be working in. Being able to transform the raw data without changing it. And, on top of that, PQ can handle huge amounts of data, but it works great on smaller data sets as well. Just love it.

 

I'm on the insider/beta MS365 version and have access to all the new and exciting DA functions, including LET, LAMBDA, MAP etc. and fully understand your drive to steer away from traditional Excel solutions. But, as you mentioned, many "out there" don't have access to them (yet).

 

Quite often, I see solutions with PQ sooner then with DA functions. Being quite a lazy person in that respect, I tend to opt for the easiest way out. So, to answer your question. No, I really don't have any advice. Your DA solution to the Voter problem demonstrates there is an overlap. But, I'm mostly driven by PQ's ease of use and lack of in depth knowledge of how to use all the new DA functions. I guess I'll get there at some point.

 

 

 

 

 

 

 

@Terry Armentrout 

One more mentioned but not demonstrated way

Let start from these cells

image.png

In K2

=IFERROR(INDEX(RegisteredVoters[ID],
   AGGREGATE(15,6,
      1/(COUNTIF(Voted[[ID]:[ID]],RegisteredVoters[[ID]:[ID]])=0)*
     (ROW(RegisteredVoters)-ROW(RegisteredVoters[[#Headers],[ID]])),
     ROW()-ROW($K$1))
), "")

You may drag it to the right into L2 and M2, or alternatively use

L2:
=IFERROR(INDEX(RegisteredVoters[Name], MATCH($K2,RegisteredVoters[ID],0)),"")

M2:
=IFERROR(INDEX(RegisteredVoters[DOB], MATCH($K2,RegisteredVoters[ID],0)),"")

and for the Age

=IFERROR(DATEDIF(M2, TODAY(),"y"),"")

Now you may select K2:N2 and drag it down till empty cells appear or enter into name box K2:N100 (this range will be selected) and Ctrl+D