Extracting duplicate data

[KateEC]

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!

 

[SteveO59L]

have you considered a pivot table?

 

[KateEC]

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

 

[bkjohn2016]

I think you may have answered your own question:

need a different table with a distinct list of employee names

 

[KateEC]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Booking Date[/TD]
[TD]Past Bookings[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]21/02/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]23/05/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]08/04/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]09/04/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]01/06/2018[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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! )

 

[nikio8]

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