Display all values of the same date vertically

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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sheet1, A:E, houses the source data.

Sheet2, A:D, displays the results of the required processing.

MonthMay
Day5
SiteFOB MILL
IDXDEALERPRODUCTQUANTITY
10AMSERCOL.LAPU500
11AMSERCOL.LAPU700
12AMSERCOG.HARV.1500
13CALOSUYS.LORD500

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4266" width=120><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3868" width=109><TBODY>
</TBODY>

A5, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(SMALL(IF(TEXT(Sheet1!$A$2:$A$16,"d-mmm")=$B$2&"-"&$B$1,
  IF(Sheet1!$C$2:$C$16=$B$3,ROW(Sheet1!$A$2:$A$16)-ROW(Sheet1!$A$2)+1)),
  ROWS($A$5:A5)),"")

B5, just enter, copy across, and down:
Rich (BB code):
=IF($A5="","",INDEX(Sheet1!$A$2:$E$16,$A5,MATCH(B$4,Sheet1!$A$1:$E$1,0)))
 
Upvote 0
Sheet1, A:E, houses the source data.

Sheet2, A:D, displays the results of the required processing.

MonthMay
Day5
SiteFOB MILL
IDXDEALERPRODUCTQUANTITY
10AMSERCOL.LAPU500
11AMSERCOL.LAPU700
12AMSERCOG.HARV.1500
13CALOSUYS.LORD500

<tbody>
</tbody>

A5, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(SMALL(IF(TEXT(Sheet1!$A$2:$A$16,"d-mmm")=$B$2&"-"&$B$1,
  IF(Sheet1!$C$2:$C$16=$B$3,ROW(Sheet1!$A$2:$A$16)-ROW(Sheet1!$A$2)+1)),
  ROWS($A$5:A5)),"")

B5, just enter, copy across, and down:
Rich (BB code):
=IF($A5="","",INDEX(Sheet1!$A$2:$E$16,$A5,MATCH(B$4,Sheet1!$A$1:$E$1,0)))

Hi Aladin,

Greetings!

As usual, you've been not only very generous in sharing your knowledge but also your promptness.


But is it possible to display the data this way?

MonthMay
Day5
SiteFOB MILL

<tbody>
</tbody>

DATE DEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORDS.BLENDA.PURPS.NODDLES.GODDC.TAILE.DRAGON
5-May-14AMSERCO12001500
5-May-14CALOSUY500

<tbody>
</tbody>

Since AMSERCO appeared twice for L.LAPU, it automatically sum up the quantity for L.LAPU (700 + 500) which makes it 1200.

Is this setup possible?
 
Upvote 0
Hi Aladin,

Greetings!

As usual, you've been not only very generous in sharing your knowledge but also your promptness.


But is it possible to display the data this way?
[...]
Since AMSERCO appeared twice for L.LAPU, it automatically sum up the quantity for L.LAPU (700 + 500) which makes it 1200.

Is this setup possible?

Only partially shown in order to spare space...

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

<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>

A6, just enter and copy down:
Rich (BB code):
=IF($B6="","",($B$2&"-"&$B$1)+0)

Define Ivec using Formulas | Name Manager as referring to:
Rich (BB code):
=ROW(Sheet1!$B$2:$B$16)-ROW(Sheet1!$B$2)+1

B6, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX(Sheet1!$B$2:$B$16,SMALL(IF(FREQUENCY(IF(Sheet1!$B$2:$B$16<>"",
  IF(TEXT(Sheet1!$A$2:$A$16,"d-mmm")=$B$2&"-"&$B$1,IF(Sheet1!$C$2:$C$16=$B$3,
  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!$A$2:$A$16,$A6,
  Sheet1!$B$2:$B$16,$B6,Sheet1!$C$2:$C$16,$B$3,Sheet1!$D$2:$D$16,C$5))

If so desired, custom format the formula cells in colum C and beyond as:

[=0]"";General
 
Upvote 0
Only partially shown in order to spare space...
MonthMay
Day5
SiteFOB MILL
DATEDEALERSL.LAPUELEF.ORO EX.G.HARV.S.LORD
5-May-14CALOSUY0000500
5-May-14AMSERCO12000015000
<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>
A6, just enter and copy down:
Rich (BB code):
=IF($B6="","",($B$2&"-"&$B$1)+0)
Define Ivec using Formulas | Name Manager as referring to:
Rich (BB code):
=ROW(Sheet1!$B$2:$B$16)-ROW(Sheet1!$B$2)+1
B6, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX(Sheet1!$B$2:$B$16,SMALL(IF(FREQUENCY(IF(Sheet1!$B$2:$B$16<>"",  IF(TEXT(Sheet1!$A$2:$A$16,"d-mmm")=$B$2&"-"&$B$1,IF(Sheet1!$C$2:$C$16=$B$3,  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!$A$2:$A$16,$A6,  Sheet1!$B$2:$B$16,$B6,Sheet1!$C$2:$C$16,$B$3,Sheet1!$D$2:$D$16,C$5))
If so desired, custom format the formula cells in colum C and beyond as:[=0]"";General
hi Aladin,I apologized but I can't get it to work.Nothing is working up in the dealer.can you create a sample file and send it here so I can see you do it?
 
Upvote 0
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.



Also, I added entries in sheet 1 the following entries after row 16

DATEDEALERPICKUP SITEPRODUCTQUANTITY
21-May-14CSCFOB MILLL.LAPU200
21-May-14VINCEEX-PASIGL.LAPU200
21-May-14MITCHEX-PASIGL.LAPU200
22-May-14CSCFOB MILLL.LAPU200

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

I extended the parameters in the formula too in sheet 2

from:
=IFERROR(INDEX(Sheet1!$B$2:$B$16,SMALL(IF(FREQUENCY(IF(Sheet1!$B$2:$B$16<>"",IF(TEXT(Sheet1!$A$2:$A$16,"d-mmm")=$B$2&"-"&$B$1,IF(Sheet1!$C$2:$C$16=$B$3,MATCH(Sheet1!$B$2:$B$16,Sheet1!$B$2:$B$16,0)))),Ivec),Ivec),ROWS($B$6:B6))),"")

to:
=IFERROR(INDEX(Sheet1!$B$2:$B$20,SMALL(IF(FREQUENCY(IF(Sheet1!$B$2:$B$20<>"",IF(TEXT(Sheet1!$A$2:$A$20,"d-mmm")=$B$2&"-"&$B$1,IF(Sheet1!$C$2:$C$20=$B$3,MATCH(Sheet1!$B$2:$B$20,Sheet1!$B$2:$B$20,0)))),Ivec),Ivec),ROWS($B$6:B6))),"")

now, when I did the search for the entries above for May 21 regardless of the pickup site, nothing shows up. I looks to me that the code works only.

I don't what I did wrong and I hope you can help me again, Aladin.
Thank you again.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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