Extracting duplicate data

KateEC

New Member
Joined
Jul 4, 2018
Messages
3
Hi all,

I am trying to find a way to extract duplicate data from one table where each value has multiple rows and get it into a table where each value has only one row.

So in Table A, I have the following columns: employee name, holiday booking date, future booking?, past booking?, sum of future bookings, sum of past bookings. So each employee has a row for every date they have booked (i.e multiple row for each value). Future and past booking columns contain a 1 respectively if the booking is future or past. The sum of future/ past bookings is duplicated per employee to provide a single figure (when averaged - seemed the best/ simplest solution to sum these 1s).

I need to get the sum of future/ past into a different table with a distinct list of employee names. Obviously as they sum of future/past are numbers rather than strings there will be more than one of each number (this table is only an example of a much larger one) so distinct formulas don't work.

I've messed around with calculate, sum, filter, allexcept, earlier etc, but I don't really understand them well enough to get it right/ know what i'm doing.. :/ just getting lots of errors!

I hope that all makes sense!

Thank you so much in advance! :)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The problem is I am in query editor because I need to get all data onto one query in order for slicers to work properly when I do put it into a pivot :(
 
Upvote 0
I think you may have answered your own question:

Sorry if I wasn't clear - I actually already have the table with the distinct list if names, but what I am struggling with is getting the duplicated data into from the table with the list of duplicated names into the list of distinct names.

I have the duplicated list and then next to each of the duplicate values, I have a column with the number of past bookings (and one with future bookings) - so if Joe Bloggs has 4 past bookings, for each row with his name, the past bookings column will say 4 for each:

NameBooking DatePast Bookings
Joe Bloggs21/02/20185
Joe Bloggs23/05/20185
Joe Bloggs08/04/20185
Joe Bloggs09/04/20185
Joe Bloggs01/06/20185

<tbody>
</tbody>

I need to add in the "5" from the past bookings column into my table with the distinct list of employee names for each employee.

Sorry if I didn't explain that very well (I've only just found the option to add in a table here! )
 
Upvote 0
You are a bit unclear.
Why not add another column that will get rid of the duplicates. Custom = Date + name. delete duplicates
Or group data,
name, past booking,
name, future bookings
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top