Help please

[GH19NEL]

Hello All,

I'm a newbie here so please don't be too harsh with me I apologise if this has been covered somewhere before, but I can't find any information on what I'm looking to achieve.

Here goes,

I'll have a Master sheet that I will populate with ID numbers, names, dates and number of days etc, so it will be updated with new data frequently. The problem being there will be duplicate ID numbers and names appearing all the time.

Ideally, what I would like it to do is auto populate a separate sheet with it pulling the ID number and name through (only once, so I'd like it to automatically remove duplicates in the process), whilst adding up the number of days associated with these multiple lines of ID numbers.
Hopefully I make sense I thought I was good at excel but I can't put my finger on the best way to approach it and actually feel a bit silly asking this question which is probably a walk in the park for most of you.

Thank you for taking the time to read this.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. 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.

 

[GH19NEL]

Hi Fluff

Thank you for taking the time to read my help request and rapid response. Unfortunately, due to using Excel at work, I'm not able to add - add ons. so the best I can do is attach the below a screenshots.

Sheet 1 will be the master.

The below will be sheet 2 and a summary (I guess), dropping the duplicates whilst adding up the no. of days from the multiple entries.

The only thing being, Sheet 1 will be constantly updated and will have duplicates appearing frequently but when sheet 1 is populated, I would like sheet 2 to update automatically based on the above.

I'm hoping this makes sense.
Thank you.

 

[Fluff]

As you cannot use the add-in, can you just copy paste the data from sheet1. Thanks

 

[GH19NEL]

As you cannot use the add-in, can you just copy paste the data from sheet1. Thanks

Thank you Fluff, for your replies. I've copied & pasted the data from sheet 1 below. I was in 2 minds whether the best way to attempt it was a formula or a VBA with a macro. Intrigued to know which way you would approach it to get the required outcome.

ID No.	Forename	Surname	Home	Where	From	To	No. of days
147586​	Mickey	Mouse	Disneyworld	Clubhouse	01/06/2022​	05/06/2022​	3​
210298​	Donald	Duck	Disneyworld	Clubhouse	05/07/2022​	08/07/2022​	2​
124896​	Clark	Kent	Smallville	Kent Farm	15/06/2022​	19/06/2022​	5​
187569​	Indiana	Jones	Colorado	Antonito	12/05/2022​	17/05/2022​	6​
126786​	Sherlock	Holmes	London	Baker Street	08/07/2022​	12/07/2022​	4​
147586​	Mickey	Mouse	Disneyworld	Clubhouse	20/06/2022​	21/06/2022​	2​
199878​	Iron	Man	California	Malibu Point	03/09/2022​	07/09/2022​	5​
165897​	Harry	Potter	Berkshire	Picket Post Close	15/03/2022​	16/03/2022​	2​
126786​	Sherlock	Holmes	London	Baker Street	30/07/2022​	02/07/2022​	3​
147586​	Mickey	Mouse	Disneyworld	Clubhouse	01/08/2022​	03/08/2022​	3​

Regards

 

[Fluff]

Thanks for that.
Two options depending on if you have hstack yet.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID No.	Forename	Surname	Home	Where	From	To	No. of days
2	147586	Mickey	Mouse	Disneyworld	Clubhouse	01/06/2022	05/06/2022	3		147586	Mickey	Mouse	Disneyworld	Clubhouse	8
3	210298	Donald	Duck	Disneyworld	Clubhouse	05/07/2022	08/07/2022	2		210298	Donald	Duck	Disneyworld	Clubhouse	2
4	124896	Clark	Kent	Smallville	Kent Farm	15/06/2022	19/06/2022	5		124896	Clark	Kent	Smallville	Kent Farm	5
5	187569	Indiana	Jones	Colorado	Antonito	12/05/2022	17/05/2022	6		187569	Indiana	Jones	Colorado	Antonito	6
6	126786	Sherlock	Holmes	London	Baker Street	08/07/2022	12/07/2022	4		126786	Sherlock	Holmes	London	Baker Street	7
7	147586	Mickey	Mouse	Disneyworld	Clubhouse	20/06/2022	21/06/2022	2		199878	Iron	Man	California	Malibu Point	5
8	199878	Iron	Man	California	Malibu Point	03/09/2022	07/09/2022	5		165897	Harry	Potter	Berkshire	Picket Post Close	2
9	165897	Harry	Potter	Berkshire	Picket Post Close	15/03/2022	16/03/2022	2
10	126786	Sherlock	Holmes	London	Baker Street	30/07/2022	02/07/2022	3
11	147586	Mickey	Mouse	Disneyworld	Clubhouse	01/08/2022	03/08/2022	3		147586	Mickey	Mouse	Disneyworld	Clubhouse	8
12										210298	Donald	Duck	Disneyworld	Clubhouse	2
13										124896	Clark	Kent	Smallville	Kent Farm	5
14										187569	Indiana	Jones	Colorado	Antonito	6
15										126786	Sherlock	Holmes	London	Baker Street	7
16										199878	Iron	Man	California	Malibu Point	5
17										165897	Harry	Potter	Berkshire	Picket Post Close	2
18
19
Sheet1

Cell Formulas
Range	Formula
J2:O8	J2	=LET(u,UNIQUE(FILTER(A2:E1000,A2:A1000<>"")),HSTACK(u,SUMIFS(H:H,A:A,INDEX(u,,1))))
J11:O17	J11	=LET(u,UNIQUE(FILTER(A2:E1000,A2:A1000<>"")),s,SEQUENCE(,COLUMNS(u)+1),IF(s<=COLUMNS(u),u,SUMIFS(H:H,A:A,INDEX(u,,1))))
Dynamic array formulas.

 

[Kerryx]

Possible other solution using a Pivot table

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID No.	Forename	Surname	Home	Where	From	To	No. of days
2	147586	Mickey	Mouse	Disneyworld	Clubhouse	01/06/2022	05/06/2022	3		Sum of No. of days
3	210298	Donald	Duck	Disneyworld	Clubhouse	05/07/2022	08/07/2022	2		ID No.	Forename	Surname	Home	Where	Total
4	124896	Clark	Kent	Smallville	Kent Farm	15/06/2022	19/06/2022	5		124896	Clark	Kent	Smallville	Kent Farm	5
5	187569	Indiana	Jones	Colorado	Antonito	12/05/2022	17/05/2022	6		126786	Sherlock	Holmes	London	Baker Street	7
6	126786	Sherlock	Holmes	London	Baker Street	08/07/2022	12/07/2022	4		147586	Mickey	Mouse	Disneyworld	Clubhouse	8
7	147586	Mickey	Mouse	Disneyworld	Clubhouse	20/06/2022	21/06/2022	2		165897	Harry	Potter	Berkshire	Picket Post Close	2
8	199878	Iron	Man	California	Malibu Point	03/09/2022	07/09/2022	5		187569	Indiana	Jones	Colorado	Antonito	6
9	165897	Harry	Potter	Berkshire	Picket Post Close	15/03/2022	16/03/2022	2		199878	Iron	Man	California	Malibu Point	5
10	126786	Sherlock	Holmes	London	Baker Street	30/07/2022	02/07/2022	3		210298	Donald	Duck	Disneyworld	Clubhouse	2
11	147586	Mickey	Mouse	Disneyworld	Clubhouse	01/08/2022	03/08/2022	3
Sheet1

 

[GH19NEL]

Thank you so much Fluff & Kerryx. I appreciate your time. I will have a look at these solutions later today as currently away from my desk.

 

[Fluff]

Ok

 

[GH19NEL]

Ok

Hi Fluff, me again

I've added your formula to my spreadsheet and copied and pasted the results below from both example formulas you provided. Thank you for the time you've taken to help. I am grateful, although I do have 1 more question (as probably most people always do haha). It ALMOST works as I would like but when I add a new line of data (I've added Mickey Mouse again) it does add the totals up in the results but doesn't recognise there is already that ID number/name so add them as a new line, it does however recognise that it needs to add the total days to both lines of data.

Is there anyway of it recognising that this name/ID number already appear in the results/summary?

ID No.

Forename

Surname

Home

Where

No. of days

147586​

Mickey

Mouse

Disneyworld

Clubhouse

10​

147586​

Mickey

Mouse

Disneyworld

Clubhouse

8​

210298​

Donald

Duck

Disneyworld

Clubhouse

2​

210298​

Donald

Duck

Disneyworld

Clubhouse

2​

124896​

Clark

Kent

Smallville

Kent Farm

5​

124896​

Clark

Kent

Smallville

Kent Farm

5​

187569​

Indiana

Jones

Colorado

Antonito

6​

187569​

Indiana

Jones

Colorado

Antonito

6​

126786​

Sherlock

Holmes

London

Baker Street

7​

126786​

Sherlock

Holmes

London

Baker Street

7​

199878​

Iron

Man

California

Malibu Point

5​

199878​

Iron

Man

California

Malibu Point

5​

165897​

Harry

Potter

Berkshire

Picket Post Close

2​

165897​

Harry

Potter

Berkshire

Picket Post Close

2​

147586​

Mickey

Mouse

Disneyworld

Clubhouse

10​

147586​

Mickey

Mouse

Disneyworld

Clubhouse

2​

147586​

Mickey

Mouse

Disneyworld

Clubhouse

10​

210298​

Donald

Duck

Disneyworld

Clubhouse

2​

124896​

Clark

Kent

Smallville

Kent Farm

5​

187569​

Indiana

Jones

Colorado

Antonito

6​

126786​

Sherlock

Holmes

London

Baker Street

7​

199878​

Iron

Man

California

Malibu Point

5​

165897​

Harry

Potter

Berkshire

Picket Post Close

2​

147586​

Mickey

Mouse

Disneyworld

Clubhouse

10​

Kind Regards