date problem

[kin]

hello

I d like to ask you if you could help me find a solution. I have two columns one called "date of order" and another "value of order" .The rows in These 2 columns are not always filled. Only when there is an order there is a date too. Now I want to find how many days on average it takes me until I have a new order. What is the date difference between each order and then the average (in days). How can I find it?Thanks

 

[kin]

I forgot to mention that it would be nice to see the extracted data in another sheet

 

[markmzz]

hello

I d like to ask if you could help me with finding which customers have not been visited for a given time period (e.g a month)
I have the following data look like:

CUSTOMER NAME DATE OF VISIT
JAMES J. 28/1/2014
KATE L. 28/1/2014
JOHN M. 28/1/2014
JAMES J. 25/3/2014
KATE L. 25/3/2014
JOHN M. 25/4/2014

I wish to retrieve the whole rows that there are for each customer. Hence I would know that one customer has not been visited in the last month or more than a month. Thank you

If I understand correctly what you want, maybe this can helps:

Layout

CUSTOMER NAME	DATE OF VISIT		Start Date	End Date	Row #	CUSTOMER NAME	DATE OF VISIT
JAMES J.	28/01/2014		01/01/2014	31/01/2014	4	JAMES J.	25/03/2014
KATE L.	28/01/2014				5	KATE L.	25/03/2014
JOHN M.	28/01/2014				6	JOHN M.	25/04/2014
JAMES J.	25/03/2014
KATE L.	25/03/2014
JOHN M.	25/04/2014
****************	*************	**	**********	***********	******	****************	*************

<colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;" span="2"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody> </tbody>

Formulas

F2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(SMALL(IF((Sheet1!$B$2:$B$7<$D$2)+(Sheet1!$B$2:$B$7>$E$2),ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(F$2:F2)),"")

And copy down.

In G2

=IF($F2="","",INDEX(Sheet1!A$2:A$7,$F2))

And copy to the right and down.

Markmzz

 

[kin]

In column E I have this =IFERROR(SMALL(IF((Sheet1!$B$2:$B$1000<$D$2)+(Sheet1!$B$2:$B$1000>$C$2);ROW(Sheet1!$B$2:$B$1000)-ROW(Sheet1!$B$2)+1);ROWS(E$2:E2));"")

in column F I have this formula =IF($E2="";"";INDEX(Sheet1!A$2:A$1000;$E2))
which gives the names

and finally In G i have =IF($F2="";"";INDEX(Sheet1!A$2:A$1000;$F2))
which returns the #VALUE! error. So i don t see the dates. Column E and F though give row number and customer name respectively

thanks

 

[markmzz]

In column E I have this =IFERROR(SMALL(IF((Sheet1!$B$2:$B$1000<$D$2)+(Sheet1!$B$2:$B$1000>$C$2);ROW(Sheet1!$B$2:$B$1000)-ROW(Sheet1!$B$2)+1);ROWS(E$2:E2));"")

in column F I have this formula =IF($E2="";"";INDEX(Sheet1!A$2:A$1000;$E2))
which gives the names

and finally In G i have =IF($F2="";"";INDEX(Sheet1!A$2:A$1000;$F2))
which returns the #VALUE! error. So i don t see the dates. Column E and F though give row number and customer name respectively

thanks

Hi Kin,

Try this:

	A	B	Sheet1	C	D	E	F	G	Sheet2
1	CUSTOMER NAME	DATE OF VISIT		Start Date	End Date	Row #	CUSTOMER NAME	DATE OF VISIT
2	JAMES J.	28/01/2014		01/01/2014	31/01/2014	4	JAMES J.	25/03/2014
3	KATE L.	28/01/2014				5	KATE L.	25/03/2014
4	JOHN M.	28/01/2014				6	JOHN M.	25/04/2014
5	JAMES J.	25/03/2014
6	KATE L.	25/03/2014
7	JOHN M.	25/04/2014
****	****************	*************	*******	**********	***********	******	****************	*************	*******

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;" span="2"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody> </tbody>

Formulas

In E2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(SMALL(IF((Sheet1!$B$2:$B$7<$C$2)+(Sheet1!$B$2:$B$7>$D$2),ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(E$2:E2)),"")

And copy down.

In F2 - use Enter to enter the formula

=IF($E2="","",INDEX(Sheet1!$A$2:$B$7,$E2,MATCH(F$1,Sheet1!$A$1:$B$1,0)))

And copy down.

In G2 - use Enter to enter the formula

=IF($E2="","",INDEX(Sheet1!$A$2:$B$7,$E2,MATCH(G$1,Sheet1!$A$1:$B$1,0)))

And copy down.

Markmzz

 

[markmzz]

Hi Kin,

Here is my full layout:

A

B

C

Sheet1

A

B

C

E

I

J

K

Sheet2

1

CUSTOMER NAME

DATE OF VISIT

1

Start Date

End Date

Row #

CUSTOMER NAME

DATE OF VISIT

2

JAMES J.

28/01/2014

2

01/01/2014

31/01/2014

4

JAMES J.

25/03/2014

3

KATE L.

28/01/2014

3

5

KATE L.

25/03/2014

4

JOHN M.

28/01/2014

4

6

JOHN M.

25/04/2014

5

JAMES J.

25/03/2014

5

6

KATE L.

25/03/2014

6

7

JOHN M.

25/04/2014

7

****

****************

*************

****

*******

****

****

****

**********

***********

******

****************

*************

*******

<tbody>
</tbody>

Markmzz