Need formulas

[Mustafabouhafs]

Need excel to automaticaly bring data from a table .. help please

 

[Eric W]

Bienvenue!

When possible, please consider using the XL2BB add-in (see the button in the reply box). This makes it so much easier for the helpers, so that they don't have to retype your picture to test with.

That said, see if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I
1	N	Nationality	ARRIVE	DEPART		Date Arrive	Nationality	New guests	Remaining guests
2	1	TUN	28-Feb	1-Mar		28-Feb	TUN	1	0
3	2	TUN	29-Feb	1-Mar		29-Feb	TUN	5	0
4	3	TUN	29-Feb	1-Mar			ALG	12	0
5	4	ALG	29-Feb	1-Mar
6	5	ALG	29-Feb	1-Mar
7	6	ALG	29-Feb	1-Mar
8	7	ALG	29-Feb	1-Mar
9	8	ALG	29-Feb	1-Mar
10	9	TUN	29-Feb	1-Mar
11	10	TUN	29-Feb	1-Mar
12	11	TUN	29-Feb	1-Mar
13	12	ALG	29-Feb	1-Mar
14	13	ALG	29-Feb	1-Mar
15	14	ALG	29-Feb	1-Mar
16	15	ALG	29-Feb	7-Mar
17	16	ALG	29-Feb	1-Mar
18	17	ALG	29-Feb	1-Mar
19	18	ALG	29-Feb	1-Mar
20	19	TUN	1-Mar	2-Mar		1-Mar	TUN	4	0
21	20	TUN	1-Mar	2-Mar			ALG	1	1
22	21	TUN	1-Mar	2-Mar
23	22	TUN	1-Mar	2-Mar
24	23	ALG	1-Mar	2-Mar
25	24	ALG	2-Mar	3-Mar		2-Mar	ALG	2	1
26	25	ALG	2-Mar	3-Mar			TUN	1	0
27	26	TUN	2-Mar	3-Mar
28
29
30
Sheet6

Cell Formulas
Range	Formula
F2:F27	F2	=IF(C2<>C1,C2,"")
G2:G27	G2	=IF(C2<>C1,B2,IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$30)/(C$2:C$30=C2)/(MATCH(B$2:B$30&C2,B$2:B$30&C$2:C$30,0)=ROW(B$2:B$30)-ROW(B$2)+1),COUNTIF(C$2:C2,C2))),""))
H2:H27	H2	=IF(G2<>"",COUNTIFS(B$2:B$30,G2,C$2:C$30,C2),"")
I2:I27	I2	=IF(G2="","",COUNTIFS(B$2:B$30,G2,C$2:C$30,"<"&C2,D$2:D$30,">"&D2))

Change the commas in the formulas to semi-colons for your regional settings.

 

[Mustafabouhafs]

Bienvenue!

When possible, please consider using the XL2BB add-in (see the button in the reply box). This makes it so much easier for the helpers, so that they don't have to retype your picture to test with.

That said, see if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I
1	N	Nationality	ARRIVE	DEPART		Date Arrive	Nationality	New guests	Remaining guests
2	1	TUN	28-Feb	1-Mar		28-Feb	TUN	1	0
3	2	TUN	29-Feb	1-Mar		29-Feb	TUN	5	0
4	3	TUN	29-Feb	1-Mar			ALG	12	0
5	4	ALG	29-Feb	1-Mar
6	5	ALG	29-Feb	1-Mar
7	6	ALG	29-Feb	1-Mar
8	7	ALG	29-Feb	1-Mar
9	8	ALG	29-Feb	1-Mar
10	9	TUN	29-Feb	1-Mar
11	10	TUN	29-Feb	1-Mar
12	11	TUN	29-Feb	1-Mar
13	12	ALG	29-Feb	1-Mar
14	13	ALG	29-Feb	1-Mar
15	14	ALG	29-Feb	1-Mar
16	15	ALG	29-Feb	7-Mar
17	16	ALG	29-Feb	1-Mar
18	17	ALG	29-Feb	1-Mar
19	18	ALG	29-Feb	1-Mar
20	19	TUN	1-Mar	2-Mar		1-Mar	TUN	4	0
21	20	TUN	1-Mar	2-Mar			ALG	1	1
22	21	TUN	1-Mar	2-Mar
23	22	TUN	1-Mar	2-Mar
24	23	ALG	1-Mar	2-Mar
25	24	ALG	2-Mar	3-Mar		2-Mar	ALG	2	1
26	25	ALG	2-Mar	3-Mar			TUN	1	0
27	26	TUN	2-Mar	3-Mar
28
29
30
Sheet6

Cell Formulas
Range	Formula
F2:F27	F2	=IF(C2<>C1,C2,"")
G2:G27	G2	=IF(C2<>C1,B2,IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$30)/(C$2:C$30=C2)/(MATCH(B$2:B$30&C2,B$2:B$30&C$2:C$30,0)=ROW(B$2:B$30)-ROW(B$2)+1),COUNTIF(C$2:C2,C2))),""))
H2:H27	H2	=IF(G2<>"",COUNTIFS(B$2:B$30,G2,C$2:C$30,C2),"")
I2:I27	I2	=IF(G2="","",COUNTIFS(B$2:B$30,G2,C$2:C$30,"<"&C2,D$2:D$30,">"&D2))

Change the commas in the formulas to semi-colons for your regional settings.

Woow .. man

Bienvenue!

When possible, please consider using the XL2BB add-in (see the button in the reply box). This makes it so much easier for the helpers, so that they don't have to retype your picture to test with.

That said, see if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I
1	N	Nationality	ARRIVE	DEPART		Date Arrive	Nationality	New guests	Remaining guests
2	1	TUN	28-Feb	1-Mar		28-Feb	TUN	1	0
3	2	TUN	29-Feb	1-Mar		29-Feb	TUN	5	0
4	3	TUN	29-Feb	1-Mar			ALG	12	0
5	4	ALG	29-Feb	1-Mar
6	5	ALG	29-Feb	1-Mar
7	6	ALG	29-Feb	1-Mar
8	7	ALG	29-Feb	1-Mar
9	8	ALG	29-Feb	1-Mar
10	9	TUN	29-Feb	1-Mar
11	10	TUN	29-Feb	1-Mar
12	11	TUN	29-Feb	1-Mar
13	12	ALG	29-Feb	1-Mar
14	13	ALG	29-Feb	1-Mar
15	14	ALG	29-Feb	1-Mar
16	15	ALG	29-Feb	7-Mar
17	16	ALG	29-Feb	1-Mar
18	17	ALG	29-Feb	1-Mar
19	18	ALG	29-Feb	1-Mar
20	19	TUN	1-Mar	2-Mar		1-Mar	TUN	4	0
21	20	TUN	1-Mar	2-Mar			ALG	1	1
22	21	TUN	1-Mar	2-Mar
23	22	TUN	1-Mar	2-Mar
24	23	ALG	1-Mar	2-Mar
25	24	ALG	2-Mar	3-Mar		2-Mar	ALG	2	1
26	25	ALG	2-Mar	3-Mar			TUN	1	0
27	26	TUN	2-Mar	3-Mar
28
29
30
Sheet6

Cell Formulas
Range	Formula
F2:F27	F2	=IF(C2<>C1,C2,"")
G2:G27	G2	=IF(C2<>C1,B2,IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$30)/(C$2:C$30=C2)/(MATCH(B$2:B$30&C2,B$2:B$30&C$2:C$30,0)=ROW(B$2:B$30)-ROW(B$2)+1),COUNTIF(C$2:C2,C2))),""))
H2:H27	H2	=IF(G2<>"",COUNTIFS(B$2:B$30,G2,C$2:C$30,C2),"")
I2:I27	I2	=IF(G2="","",COUNTIFS(B$2:B$30,G2,C$2:C$30,"<"&C2,D$2:D$30,">"&D2))

Change the commas in the formulas to semi-colons for your regional settings.

wooow .. Man you're genius .. thats impossible .. thanks and god bless

 

[Eric W]

Glad to help!