Display all values of the same date vertically

L

Lifecoachlee

New Member
Joined
May 2, 2014
Messages
36
Hi everyone,

I need some help so badly!

Here's a sample of the project I am working on with.

Sheet 1
----------------------------------------------------------------------

DATE		DEALERPICKUP SITEPRODUCTQUANTITY
1-Apr-14ACAPARROSEX-PASIGL.LAPU300
2-Apr-14CALOSUYEX-PASIGL.LAPU200
3-Apr-14AMSERCOFOB MILLELEF.500
12-Apr-14ASIATICEX-PASIGL.LAPU700
16-Apr-14ACAPARROSEX-PASIGELEF.1500
1-May-14ASMARTEX-PASIGELEF.1500
2-May-14ASMKTGEX-PASIGG.HARV.1500
3-May-14CALOSUYEX-PASIGS.BLEND300
4-May-14AMSERCOFOB MILLL.LAPU200
5-May-14AMSERCOFOB MILLL.LAPU500
5-May-14AMSERCOFOB MILLL.LAPU700
5-May-14AMSERCOFOB MILLG.HARV.1500
5-May-14CALOSUYFOB MILLS.LORD500
13-May-14AMSERCOFOB MILLELEF.300
20-May-14AMSERCOFOB MILLL.LAPU300

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2
----------------------------------------------------------------------------
MONTH:APRILDAY:SITE:EX-PASIG
DEALERDATEL.LAPUELEF.ORO EX.G.HARV.S.LORDS.BLENDA.PURP.S.NOODLES.GODD.C.TAILE.DRAGON

<colgroup><col span="11"><col><col></colgroup><tbody>
</tbody>



Here's what I wanted to happen.

Month, Day and Site have their corresponding data validation list where you can pull up the records to display under the table above once criteria are met and automatically add the values of the same products of the same date.

Example:
I will select from the list the ff in the search criteria:

Month: May
Day: 5
Site: FOB MILL

It should display like this:

DEALER DATE L.LAPUELEF.ORO EX.G.HARV.S.LORDS.BLENDA.PURP.S.NOODLES.GODD.C.TAILE.DRAGON

<tbody>
</tbody>
AMSERCO 5-May-14 1200 1500
CALOSUY 5-May-14 500



Please help. Thank you!
 
Sort by date Sort by votes
Lifecoachlee said:
Hi Aladin,

When I tried to apply to my worksheet, it's taking so long to calculate. Is this normal?
I extended the formula in sheet 2 for over a 100 cells. Could that be the problem?
Please advise. Thank you.
Click to expand...


The output set up you opted for requires intensive array-processing formulas...

Let's try to speed up the workbook a bit...

Sheet1

DATE
DEALER
PICKUP SITE
PRODUCT
QUANTITY
CONCAT (date|dealer|site|product)
1-Apr-14
ACAPARROS
EX-PASIG
L.LAPU
300
41730|ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14
CALOSUY
EX-PASIG
L.LAPU
200
41731|CALOSUY|EX-PASIG|L.LAPU
3-Apr-14
AMSERCO
FOB MILL
ELEF.
500
41732|AMSERCO|FOB MILL|ELEF.
12-Apr-14
ASIATIC
EX-PASIG
L.LAPU
700
41741|ASIATIC|EX-PASIG|L.LAPU
16-Apr-14
ACAPARROS
EX-PASIG
ELEF.
1500
41745|ACAPARROS|EX-PASIG|ELEF.
1-May-14
ASMART
EX-PASIG
ELEF.
1500
41760|ASMART|EX-PASIG|ELEF.
2-May-14
ASMKTG
EX-PASIG
G.HARV.
1500
41761|ASMKTG|EX-PASIG|G.HARV.
3-May-14
CALOSUY
EX-PASIG
S.BLEND
300
41762|CALOSUY|EX-PASIG|S.BLEND
4-May-14
AMSERCO
FOB MILL
L.LAPU
200
41763|AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
L.LAPU
500
41764|AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
L.LAPU
700
41764|AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
G.HARV.
1500
41764|AMSERCO|FOB MILL|G.HARV.
5-May-14
CALOSUY
FOB MILL
S.LORD
500
41764|CALOSUY|FOB MILL|S.LORD
13-May-14
AMSERCO
FOB MILL
ELEF.
300
41772|AMSERCO|FOB MILL|ELEF.
20-May-14
AMSERCO
FOB MILL
L.LAPU
300
41779|AMSERCO|FOB MILL|L.LAPU

<TBODY>
</TBODY>

F2, just enter and copy down:
Rich (BB code): 
=A2&"|"&B2&"|"&C2&"|"&D2

Sheet2

Month
May
Day
5
Site
FOB MILL
5-May-14
DATE
DEALERS
L.LAPU
ELEF.
ORO EX.
G.HARV.
S.LORD
5-May-14
CALOSUY
0
0
0
0
500
5-May-14
AMSERCO
1200
0
0
1500
0

<TBODY>
</TBODY>

B4, just enter:
Rich (BB code): 

=(B2&"-"&B1)+0

A6, just enter and copy down:
Rich (BB code): 
=IF($B6="","",$B$4)

B6, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$4&"|"&"?*"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)),Ivec),Ivec),ROWS($B$6:B6))),"")

Note. We have still Ivec. And the formula is considerably less intensive.

C6, just enter, copy across, and down:
Rich (BB code): 
=IF($B6="","",SUMIFS(Sheet1!$E$2:$E$16,Sheet1!$F$2:$F$16,$B$4&"|"&$B6&"|"&$B$3&"|"&C$5))

Note. This formula is now far less expensive.

See the workbook that implements the new scheme, outlined above:
https://dl.dropboxusercontent.com/u...es of the same date vertically version-2.xlsx
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Aladin Akyurek said:
The output set up you opted for requires intensive array-processing formulas...

Let's try to speed up the workbook a bit...

Sheet1

DATEDEALERPICKUP SITEPRODUCTQUANTITYCONCAT (date|dealer|site|product)
1-Apr-14ACAPARROSEX-PASIGL.LAPU30041730|ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14CALOSUYEX-PASIGL.LAPU20041731|CALOSUY|EX-PASIG|L.LAPU
3-Apr-14AMSERCOFOB MILLELEF.50041732|AMSERCO|FOB MILL|ELEF.
12-Apr-14ASIATICEX-PASIGL.LAPU70041741|ASIATIC|EX-PASIG|L.LAPU
16-Apr-14ACAPARROSEX-PASIGELEF.150041745|ACAPARROS|EX-PASIG|ELEF.
1-May-14ASMARTEX-PASIGELEF.150041760|ASMART|EX-PASIG|ELEF.
2-May-14ASMKTGEX-PASIGG.HARV.150041761|ASMKTG|EX-PASIG|G.HARV.
3-May-14CALOSUYEX-PASIGS.BLEND30041762|CALOSUY|EX-PASIG|S.BLEND
4-May-14AMSERCOFOB MILLL.LAPU20041763|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU50041764|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU70041764|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLG.HARV.150041764|AMSERCO|FOB MILL|G.HARV.
5-May-14CALOSUYFOB MILLS.LORD50041764|CALOSUY|FOB MILL|S.LORD
13-May-14AMSERCOFOB MILLELEF.30041772|AMSERCO|FOB MILL|ELEF.
20-May-14AMSERCOFOB MILLL.LAPU30041779|AMSERCO|FOB MILL|L.LAPU

<tbody>
</tbody>

F2, just enter and copy down:
Rich (BB code): 
=A2&"|"&B2&"|"&C2&"|"&D2

Sheet2

MonthMay
Day5
SiteFOB MILL
5-May-14
DATEDEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORD
5-May-14CALOSUY0000500
5-May-14AMSERCO12000015000

<tbody>
</tbody>

B4, just enter:
Rich (BB code): 

=(B2&"-"&B1)+0

A6, just enter and copy down:
Rich (BB code): 
=IF($B6="","",$B$4)

B6, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$4&"|"&"?*"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)),Ivec),Ivec),ROWS($B$6:B6))),"")

Note. We have still Ivec. And the formula is considerably less intensive.

C6, just enter, copy across, and down:
Rich (BB code): 
=IF($B6="","",SUMIFS(Sheet1!$E$2:$E$16,Sheet1!$F$2:$F$16,$B$4&"|"&$B6&"|"&$B$3&"|"&C$5))

Note. This formula is now far less expensive.

See the workbook that implements the new scheme, outlined above:
https://dl.dropboxusercontent.com/u...es of the same date vertically version-2.xlsx
Click to expand...

You're right! I saw the excel file you made for me. This is a great help.
Right now, I am trying to figure you if it is possible to display all the dealers let us say for the month of May using the formula you made.
Looks like this will only work for a specific target but not when you have range of dates.
 
Upvote 0
Lifecoachlee said:
You're right! I saw the excel file you made for me. This is a great help.
Right now, I am trying to figure you if it is possible to display all the dealers let us say for the month of May using the formula you made.
Looks like this will only work for a specific target but not when you have range of dates.
Click to expand...

Good grace...

Sheet1

DATEDEALERPICKUP SITEPRODUCTQUANTITYCONCAT (date|dealer|site|product)
1-Apr-14ACAPARROSEX-PASIGL.LAPU30001Apr14|ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14CALOSUYEX-PASIGL.LAPU20002Apr14|CALOSUY|EX-PASIG|L.LAPU
3-Apr-14AMSERCOFOB MILLELEF.50003Apr14|AMSERCO|FOB MILL|ELEF.
12-Apr-14ASIATICEX-PASIGL.LAPU70012Apr14|ASIATIC|EX-PASIG|L.LAPU
16-Apr-14ACAPARROSEX-PASIGELEF.150016Apr14|ACAPARROS|EX-PASIG|ELEF.
1-May-14ASMARTEX-PASIGELEF.150001May14|ASMART|EX-PASIG|ELEF.
2-May-14ASMKTGEX-PASIGG.HARV.150002May14|ASMKTG|EX-PASIG|G.HARV.
3-May-14CALOSUYEX-PASIGS.BLEND30003May14|CALOSUY|EX-PASIG|S.BLEND
4-May-14AMSERCOFOB MILLL.LAPU20004May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU50005May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU70005May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLG.HARV.150005May14|AMSERCO|FOB MILL|G.HARV.
5-May-14CALOSUYFOB MILLS.LORD50005May14|CALOSUY|FOB MILL|S.LORD
13-May-14AMSERCOFOB MILLELEF.30013May14|AMSERCO|FOB MILL|ELEF.
20-May-14AMSERCOFOB MILLL.LAPU30020May14|AMSERCO|FOB MILL|L.LAPU

<COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3669" width=103><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4579" width=129><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5347" width=150><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4152" width=117><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4522" width=127><COL style="WIDTH: 282pt; mso-width-source: userset; mso-width-alt: 13368" width=376><TBODY>
</TBODY>

F2, just enter and copy down:
Rich (BB code): 
=TEXT(A2,"ddmmmyy")&"|"&B2&"|"&C2&"|"&D2

Sheet2

MonthMay
Day0
SiteFOB MILL
May14
DATEDEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORD
May14CALOSUY0000500
May14AMSERCO1700300015000

<COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4408" width=124><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4778" width=134><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4380" width=123><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4124" width=116><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><TBODY>
</TBODY>

B2 = 0 means all of the days of May in B1.

B2 = 5 (a non-zero value) means the date of May 5th.

B4, just enter:
Rich (BB code): 
=IF(B2,(B2&"-"&B1)+0,TEXT((1&"-"&B1)+0,"Mmmyy"))

A6, just enter and copy down:
Rich (BB code): 
=IF($B6="","",$B$4)

B6, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(IF(ISNUMBER($B$4),TEXT($B$4,"ddmmmyy"),
  "??"&$B$4)&"|"&"?*"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)),Ivec),Ivec),ROWS($B$6:B6))),"")

C6, just enter, copy across, and down:
Rich (BB code): 
=IF($B6="","",SUMIFS(Sheet1!$E$2:$E$16,
  Sheet1!$F$2:$F$16,IF(ISNUMBER($B$4),
  TEXT($B$4,"ddmmmyy"),"??"&$B$4)&"|"&$B6&"|"&$B$3&"|"&C$5))

The workbook that implements all of the above is here:

https://dl.dropboxusercontent.com/u/65698317/Lifecoachlee%20Display%20all%20values%20of%20the%20same%20date%20vertically%20version-3.xlsx
 
Upvote 0
Aladin Akyurek said:
Good grace...

Sheet1

DATEDEALERPICKUP SITEPRODUCTQUANTITYCONCAT (date|dealer|site|product)
1-Apr-14ACAPARROSEX-PASIGL.LAPU30001Apr14|ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14CALOSUYEX-PASIGL.LAPU20002Apr14|CALOSUY|EX-PASIG|L.LAPU
3-Apr-14AMSERCOFOB MILLELEF.50003Apr14|AMSERCO|FOB MILL|ELEF.
12-Apr-14ASIATICEX-PASIGL.LAPU70012Apr14|ASIATIC|EX-PASIG|L.LAPU
16-Apr-14ACAPARROSEX-PASIGELEF.150016Apr14|ACAPARROS|EX-PASIG|ELEF.
1-May-14ASMARTEX-PASIGELEF.150001May14|ASMART|EX-PASIG|ELEF.
2-May-14ASMKTGEX-PASIGG.HARV.150002May14|ASMKTG|EX-PASIG|G.HARV.
3-May-14CALOSUYEX-PASIGS.BLEND30003May14|CALOSUY|EX-PASIG|S.BLEND
4-May-14AMSERCOFOB MILLL.LAPU20004May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU50005May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU70005May14|AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLG.HARV.150005May14|AMSERCO|FOB MILL|G.HARV.
5-May-14CALOSUYFOB MILLS.LORD50005May14|CALOSUY|FOB MILL|S.LORD
13-May-14AMSERCOFOB MILLELEF.30013May14|AMSERCO|FOB MILL|ELEF.
20-May-14AMSERCOFOB MILLL.LAPU30020May14|AMSERCO|FOB MILL|L.LAPU

<tbody>
</tbody>

F2, just enter and copy down:
Rich (BB code): 
=TEXT(A2,"ddmmmyy")&"|"&B2&"|"&C2&"|"&D2

Sheet2

MonthMay
Day0
SiteFOB MILL
May14
DATEDEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORD
May14CALOSUY0000500
May14AMSERCO1700300015000

<tbody>
</tbody>

B2 = 0 means all of the days of May in B1.

B2 = 5 (a non-zero value) means the date of May 5th.

B4, just enter:
Rich (BB code): 
=IF(B2,(B2&"-"&B1)+0,TEXT((1&"-"&B1)+0,"Mmmyy"))

A6, just enter and copy down:
Rich (BB code): 
=IF($B6="","",$B$4)

B6, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(IF(ISNUMBER($B$4),TEXT($B$4,"ddmmmyy"),
  "??"&$B$4)&"|"&"?*"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)),Ivec),Ivec),ROWS($B$6:B6))),"")

C6, just enter, copy across, and down:
Rich (BB code): 
=IF($B6="","",SUMIFS(Sheet1!$E$2:$E$16,
  Sheet1!$F$2:$F$16,IF(ISNUMBER($B$4),
  TEXT($B$4,"ddmmmyy"),"??"&$B$4)&"|"&$B6&"|"&$B$3&"|"&C$5))

The workbook that implements all of the above is here:

https://dl.dropboxusercontent.com/u/65698317/Lifecoachlee%20Display%20all%20values%20of%20the%20same%20date%20vertically%20version-3.xlsx
Click to expand...

Wow!!! This is perfect!
Your such a great help, Aladin! ;)
 
Upvote 0
Sorry for this but I have another question about date range.
What if I'd like to add up the quantity for L.LAPU example which falls from May 1 up to May 31?
Is that workable too?
 
Upvote 0
Aladin Akyurek said:
But that's what the set up does. L.LAPU total is really 1700 given the source the set up looks at!...
Click to expand...

Yeah, I got.
This formula computes the total of the product for the entire month.
My question was wrong. I extremely apologized.
What I wanted my friend is that I have to choice to select a date range aside from the entire month.
Let's say,

Start Date: 1-May-14
End Date: 10-May-14

Then it generates the report.

Again, I am sorry for taking much of your time.
Thank you for your generosity.
 
Upvote 0
Lifecoachlee said:
Are you still there, Aladin?
I hope you can help me with this.
Click to expand...

Lifecoachlee said:
Yeah, I got.
This formula computes the total of the product for the entire month.
My question was wrong. I extremely apologized.
What I wanted my friend is that I have to choice to select a date range aside from the entire month.
Let's say,

Start Date: 1-May-14
End Date: 10-May-14

Then it generates the report.

Again, I am sorry for taking much of your time.
Thank you for your generosity.
Click to expand...

With the from date to date specs, the performance won't be as good as the last two versions...

Sheet1

DATE
DEALER
PICKUP SITE
PRODUCT
QUANTITY
CONCAT (dealer|site|product)
1-Apr-14
ACAPARROS
EX-PASIG
L.LAPU
300
ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14
CALOSUY
EX-PASIG
L.LAPU
200
CALOSUY|EX-PASIG|L.LAPU
3-Apr-14
AMSERCO
FOB MILL
ELEF.
500
AMSERCO|FOB MILL|ELEF.
12-Apr-14
ASIATIC
EX-PASIG
L.LAPU
700
ASIATIC|EX-PASIG|L.LAPU
16-Apr-14
ACAPARROS
EX-PASIG
ELEF.
1500
ACAPARROS|EX-PASIG|ELEF.
1-May-14
ASMART
EX-PASIG
ELEF.
1500
ASMART|EX-PASIG|ELEF.
2-May-14
ASMKTG
EX-PASIG
G.HARV.
1500
ASMKTG|EX-PASIG|G.HARV.
3-May-14
CALOSUY
EX-PASIG
S.BLEND
300
CALOSUY|EX-PASIG|S.BLEND
4-May-14
AMSERCO
FOB MILL
L.LAPU
200
AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
L.LAPU
500
AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
L.LAPU
700
AMSERCO|FOB MILL|L.LAPU
5-May-14
AMSERCO
FOB MILL
G.HARV.
1500
AMSERCO|FOB MILL|G.HARV.
5-May-14
CALOSUY
FOB MILL
S.LORD
500
CALOSUY|FOB MILL|S.LORD
13-May-14
AMSERCO
FOB MILL
ELEF.
300
AMSERCO|FOB MILL|ELEF.
20-May-14
AMSERCO
FOB MILL
L.LAPU
300
AMSERCO|FOB MILL|L.LAPU

<TBODY>
</TBODY>

F2, copied down:
Rich (BB code): 
=B2&"|"&C2&"|"&D2

Sheet2 (shown in part)

From
1-May-14
To
10-May-14
Site
FOB MILL
DEALERS
L.LAPU
ELEF.
ORO EX.
G.HARV.
S.LORD
CALOSUY
0
0
0
0
500
AMSERCO
1400
0
0
1500
0

<TBODY>
</TBODY>

A6, control+shift+enter and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$16>=$B$1,IF(Sheet1!$A$2:$A$16<=$B$2,
  IF(ISNUMBER(SEARCH("?*|"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)))),Ivec),Ivec),
  ROWS($A$6:A6))),"")

B6, copied across and down:
Rich (BB code): 
=IF($A6="","",SUMIFS(Sheet1!$E$2:$E$16,Sheet1!$A$2:$A$16,">="&$B$1,
  Sheet1!$A$2:$A$16,"<="&$B$2,Sheet1!$F$2:$F$16,$A6&"|"&$B$3&"|"&B$5))

The workbook for this From-To version as described above:
https://dl.dropboxusercontent.com/u...the same date vertically From To version.xlsx
 
Last edited:
Upvote 0
Aladin Akyurek said:
With the from date to date specs, the performance won't be as good as the last two versions...

Sheet1

DATEDEALERPICKUP SITEPRODUCTQUANTITYCONCAT (dealer|site|product)
1-Apr-14ACAPARROSEX-PASIGL.LAPU300ACAPARROS|EX-PASIG|L.LAPU
2-Apr-14CALOSUYEX-PASIGL.LAPU200CALOSUY|EX-PASIG|L.LAPU
3-Apr-14AMSERCOFOB MILLELEF.500AMSERCO|FOB MILL|ELEF.
12-Apr-14ASIATICEX-PASIGL.LAPU700ASIATIC|EX-PASIG|L.LAPU
16-Apr-14ACAPARROSEX-PASIGELEF.1500ACAPARROS|EX-PASIG|ELEF.
1-May-14ASMARTEX-PASIGELEF.1500ASMART|EX-PASIG|ELEF.
2-May-14ASMKTGEX-PASIGG.HARV.1500ASMKTG|EX-PASIG|G.HARV.
3-May-14CALOSUYEX-PASIGS.BLEND300CALOSUY|EX-PASIG|S.BLEND
4-May-14AMSERCOFOB MILLL.LAPU200AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU500AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLL.LAPU700AMSERCO|FOB MILL|L.LAPU
5-May-14AMSERCOFOB MILLG.HARV.1500AMSERCO|FOB MILL|G.HARV.
5-May-14CALOSUYFOB MILLS.LORD500CALOSUY|FOB MILL|S.LORD
13-May-14AMSERCOFOB MILLELEF.300AMSERCO|FOB MILL|ELEF.
20-May-14AMSERCOFOB MILLL.LAPU300AMSERCO|FOB MILL|L.LAPU

<tbody>
</tbody>

F2, copied down:
Rich (BB code): 
=B2&"|"&C2&"|"&D2

Sheet2 (shown in part)

From1-May-14
To10-May-14
SiteFOB MILL
DEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORD
CALOSUY0000500
AMSERCO14000015000

<tbody>
</tbody>

A6, control+shift+enter and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$B$2:$B$16,
  SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$16>=$B$1,IF(Sheet1!$A$2:$A$16<=$B$2,
  IF(ISNUMBER(SEARCH("?*|"&$B$3&"|?*",Sheet1!$F$2:$F$16)),
  MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)))),Ivec),Ivec),
  ROWS($A$6:A6))),"")

B6, copied across and down:
Rich (BB code): 
=IF($A6="","",SUMIFS(Sheet1!$E$2:$E$16,Sheet1!$A$2:$A$16,">="&$B$1,
  Sheet1!$A$2:$A$16,"<="&$B$2,Sheet1!$F$2:$F$16,$A6&"|"&$B$3&"|"&B$5))

The workbook for this From-To version as described above:
https://dl.dropboxusercontent.com/u/65698317/Lifecoachlee%20Display%20all%20values%20of%20the%20same%20date%20vertically%20From%20To%20version.xlsx
Click to expand...

Hi Aladin,

This is awesome! You are truly amazing!
THANK YOU!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Need help here: SELECTING AND PULLING ALL DATA FOR THE GIVEN CRITERIAS
Replies
5
Views
300
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,215,181
Messages
6,123,508
Members
449,101
Latest member
mgro123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top