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]

I suspect that some of the IDs are text & some are numbers.

 

[GH19NEL]

Hi Fluff
Apologies in the delay, it works. Not that I ever doubted it. I've attempted trying to bring in another column that contained values (column I in the above example) I am starting to understand it, but I can't manage to get it to work. Should I be adding another HSTACK with a sumifs again?
Thank you for your time in helping me.

 

[Fluff]

To add another column just include it in the Hstack, like

=LET(u,UNIQUE(FILTER(A2:E1000,A2:A1000<>"")),HSTACK(u,SUMIFS(H:H,A:A,INDEX(u,,1)),someother formula))

 

[GH19NEL]

Fluff this is perfect. Apologies, if you don't mind, I have another question? If the same ID number appeared multiple times but on occasions, the 'home' or 'where' column was different, (for e.g Mickey Mouse appears above as Home = Disneyworld and where = Clubhouse) If he happens to be Home = London & Where = Baker Street, it will include this new line of data although it's a duplicate 'ID number'. Is it possible to not include in the summary as it's a duplicate? or does this become more complex formula?
I appreciate all your help and this has really helped my development too and I'm starting to understand more.
Kind Regards

 

[Fluff]

If the Home & Where are different which one should be dropped?

 

[GH19NEL]

If the Home & Where are different which one should be dropped?

Potentially, both could be different, but I don't want to over complicate, so lets go for where, as I can workaround the other column or potentially take it out.

 

[Fluff]

What I mean is if you have something like

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	ID No.	Forename	Surname	Home	Where	From	To	No. of days
2	147586	Mickey	Mouse	Bristol	City Road	44713	44717	3
7	147586	Mickey	Mouse	Disneyworld	Clubhouse	44732	44733	2
Data

Which of these two rows should be displayed?

 

[GH19NEL]

Oh sorry, Row E please.

 

[Fluff]

E is a column not a row, so do you want to see row 2 or row 7, or doesn't it matter which?

 

[GH19NEL]

E is a column not a row, so do you want to see row 2 or row 7, or doesn't it matter which?

Apologies, a mistype. We will go for Row 2 please.