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
This would be my first post on this forum, but still I would like to make a suggestion. When I tried to use the FREQUENCY function to find unique values in almost 20,000 (!) records, it took about 1 hour or so before my computer was finished calculating.
As an alternative, I used the VLOOKUP function.

For the case in this thread, I would add a column (F) with dealer names of selected data, like:
=IF(AND(TEXT(A2,"d-mmm")=Sheet2!$B$2&"-"&Sheet2!$B$1,OR(C2=Sheet2!$B$3,Sheet2!$B$3="")),B2,"")

and another column (G) with:
=G1+1*AND(F2<>"",ISERROR(VLOOKUP(F2,F$1:F1,1,0)))

The trick here is that VLOOKUP will look in all preceding records in column F and add 1 to the previous value in column G (starting with 0 in G1) for each new dealer in column F. (Make sure the column G header won't contain the name of a real dealer ;)).

For May 5, this will result in (sorry I'm dutch and the date formats are d-m-yyyy):

DATEDEALERPICKUP SITEPRODUCTQUANTITYSelected Dealer0
3-5-2014CALOSUYEX-PASIGS.BLEND3000
4-5-2014AMSERCOFOB MILLL.LAPU2000
5-5-2014AMSERCOFOB MILLL.LAPU500AMSERCO1
5-5-2014AMSERCOFOB MILLL.LAPU700AMSERCO1
5-5-2014AMSERCOFOB MILLG.HARV.1500AMSERCO1
5-5-2014CALOSUYFOB MILLS.LORD500CALOSUY2
13-5-2014AMSERCOFOB MILLELEF.3002

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

In sheet2, the unique dealers are determined by looking for 1, 2, etcetera in column G of sheet1:
For the DATE field in Sheet2!A5:
=IF(ROWS(A$5:A5)>MAX(Sheet1!G:G),"",INDEX(Sheet1!A:A,MATCH(ROWS(A$5:A5),Sheet1!G:G,0)))
For the DEALER field in Sheet2!B5:
=IF(A5="","",INDEX(Sheet1!F:F,MATCH(ROWS(A$5:A5),Sheet1!G:G,0)))
For the QUANTITY field in Sheet2!C5:
=IF($B5="","",SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,$A5,Sheet1!$B:$B,$B5,Sheet1!$D:$D,C$4))

How about this trick of using VLOOKUP to determine unique values?
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Aladin,

I have noticed something with the excel file you sent.
When I add new entry in sheet 1 and extended the parameters in sheets 2, nothing is gonna show up anymore. I dunno if it has something to do with the format of the cells.
Please help. Thanks

Example:

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>

20-May-14 LHEN FOB MILL L.LAPU 300 AMSERCO|FOB MILL|L.LAPU (new entry in row 17)

Changes made in the formula to cater up to row 20

B6

=IF($A6="","",SUMIFS(Sheet1!$E$2:$E$20,Sheet1!$A$2:$A$20,">="&$B$1, Sheet1!$A$2:$A$20,"<="&$B$2,Sheet1!$F$2:$F$20,$A6&"|"&$B$3&"|"&B$5))</pre>
A6

=IFERROR(INDEX(Sheet1!$B$2:$B$20, SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$20>=$B$1,IF(Sheet1!$A$2:$A$20<=$B$2, IF(ISNUMBER(SEARCH("?*|"&$B$3&"|?*",Sheet1!$F$2:$F$20)), MATCH(Sheet1!$B$2:$B$20,Sheet1!$B$2:$B$20,0)))),Ivec),Ivec), ROWS($A$6:A6))),"")</pre>
 
Upvote 0
Lifecoachlee said:
Aladin,

I have noticed something with the excel file you sent.
When I add new entry in sheet 1 and extended the parameters in sheets 2, nothing is gonna show up anymore. I dunno if it has something to do with the format of the cells.
Please help. Thanks

[....]

20-May-14 LHEN FOB MILL L.LAPU 300 AMSERCO|FOB MILL|L.LAPU (new entry in row 17)

Changes made in the formula to cater up to row 20

B6

=IF($A6="","",SUMIFS(Sheet1!$E$2:$E$20,Sheet1!$A$2:$A$20,">="&$B$1, Sheet1!$A$2:$A$20,"<="&$B$2,Sheet1!$F$2:$F$20,$A6&"|"&$B$3&"|"&B$5))

</PRE>
A6

=IFERROR(INDEX(Sheet1!$B$2:$B$20, SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$20>=$B$1,IF(Sheet1!$A$2:$A$20<=$B$2, IF(ISNUMBER(SEARCH("?*|"&$B$3&"|?*",Sheet1!$F$2:$F$20)), MATCH(Sheet1!$B$2:$B$20,Sheet1!$B$2:$B$20,0)))),Ivec),Ivec), ROWS($A$6:A6))),"")

</PRE>
Click to expand...

That record should have no effect at all. Neither extending the range. See the workbook the earlier link provides.
 
Upvote 0
Last edited:
Upvote 0
MarcelBeug said:
This would be my first post on this forum, but still I would like to make a suggestion. When I tried to use the FREQUENCY function to find unique values in almost 20,000 (!) records, it took about 1 hour or so before my computer was finished calculating.
As an alternative, I used the VLOOKUP function.

For the case in this thread, I would add a column (F) with dealer names of selected data, like:
=IF(AND(TEXT(A2,"d-mmm")=Sheet2!$B$2&"-"&Sheet2!$B$1,OR(C2=Sheet2!$B$3,Sheet2!$B$3="")),B2,"")

and another column (G) with:
=G1+1*AND(F2<>"",ISERROR(VLOOKUP(F2,F$1:F1,1,0)))

The trick here is that VLOOKUP will look in all preceding records in column F and add 1 to the previous value in column G (starting with 0 in G1) for each new dealer in column F. (Make sure the column G header won't contain the name of a real dealer ;)).

For May 5, this will result in (sorry I'm dutch and the date formats are d-m-yyyy):

DATEDEALERPICKUP SITEPRODUCTQUANTITYSelected Dealer0
3-5-2014CALOSUYEX-PASIGS.BLEND3000
4-5-2014AMSERCOFOB MILLL.LAPU2000
5-5-2014AMSERCOFOB MILLL.LAPU500AMSERCO1
5-5-2014AMSERCOFOB MILLL.LAPU700AMSERCO1
5-5-2014AMSERCOFOB MILLG.HARV.1500AMSERCO1
5-5-2014CALOSUYFOB MILLS.LORD500CALOSUY2
13-5-2014AMSERCOFOB MILLELEF.3002

<tbody>
</tbody>

In sheet2, the unique dealers are determined by looking for 1, 2, etcetera in column G of sheet1:
For the DATE field in Sheet2!A5:
=IF(ROWS(A$5:A5)>MAX(Sheet1!G:G),"",INDEX(Sheet1!A:A,MATCH(ROWS(A$5:A5),Sheet1!G:G,0)))
For the DEALER field in Sheet2!B5:
=IF(A5="","",INDEX(Sheet1!F:F,MATCH(ROWS(A$5:A5),Sheet1!G:G,0)))
For the QUANTITY field in Sheet2!C5:
=IF($B5="","",SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,$A5,Sheet1!$B:$B,$B5,Sheet1!$D:$D,C$4))

How about this trick of using VLOOKUP to determine unique values?
Click to expand...

Hi Markbeug,

I tried to use your code also but can't seem to get any luck.
Column G is showing all zeros.
Can you create a sample file and post it here? Thanks.
 
Upvote 0
Just tell me how to do that (I'm new here). Please note I will be off to work now so I won't reply immediately.
 
Upvote 0
Lifecoachlee said:
Hi there Aladin,

I've noticed that when I extended the range of the data, it starts to get slow.
Like I extended the data to 1000, the calculation starts to get slow.
Is there any other work around you think?

Here is the file what I am working with. You might wanna take a look at it.

https://dl.dropboxusercontent.com/s...ua_F-CLPj9DfHmYiDj80-PGWFEg&expiry=1401105261

Please let me know your thoughts.
Thanks!
Click to expand...

Cleaned up for uniformity:
https://dl.dropboxusercontent.com/u/65698317/FLOUR MILL MARKETING SYSTEM v8.xlsm

Using dynamic named ranges would be better than copying down as possibly needed.
 
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,166
Messages
6,123,395
Members
449,098
Latest member
ArturS75

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