Sorting Dates and Three Digit Codes

[Papi]

Hello,

I have a couple of things that I am trying to do. Although the two worksheets work together I would like to clean them up as I am forcing columns to make it work as I failed to figure out how to put things together properly. For instance, I created column E to identify the month even though I am sure there is a way to look at the date from column B and do the same by collecting the data by each month in the YTD worksheet. (Excel 2010). One thing to mention is that the data in this sheet comes from a database and the dates are normally in TEXT format.

As well, I forced Column F to produce a three letter code to create a column that will show which supplier based on the first three digits of the account code which typically matches the first three letters of the Supplier Code. I figure there is a way to look at the Supplier Code and extract the first three digits but have been unable to do so.

Purchase Orders.xlsm
	A	B	C	D	E	F
1	PONo.	Date	SupplierCode	Total	MMM	SC
2	1032	30May11	EDM001	708.71	May	EDM
3	1059	30May11	MEDH001	204.64	May	MED
4	1902	31Jul11	MEDH001	101.40	Jul	MED
5	1956	31Jul11	REDD001	162.90	Jul	RED
6	1046	25Jul11	MEDH001	1,875.06	Jul	MED
7	1877	04Jun11	REDD001	101.40	Jun	RED
8	1279	31May11	EDM001	129.08	May	EDM
9	1392	31Jul11	GEN001	129.08	Jul	GEN
10	1093	27Apr11	REDD001	464.25	Apr	RED
11	1031	15Aug11	KIN001	82.95	Aug	KIN
12	1976	15Aug11	REDD001	562.65	Aug	RED
13	1934	15Aug11	GRE001	562.65	Aug	GRE
14	1980	15Aug11	MEDH001	707.18	Aug	MED
15	2012	31Aug11	REDD001	255.15	Aug	RED
Main

Purchase Orders.xlsm
	A	B	C	D	E	F	G
1	Supplier	Apr	May	Jun	Jul	Aug	Total
2	EdmontonSteel		837.79				837.79
3	MediCentre		204.64		1,976.46	707.18	2,888.28
4	RedRange	464.25		101.40	162.90	817.80	1,546.35
5	GeneralCity				129.08		129.08
6	GreatWest					562.65	562.65
7	Kindle					82.95	82.95
8	Totals	464.25	1,042.43	101.40	2,268.44	2,170.58	6,047.10
YTD

 

[CWatts]

In Column E you can use: =TEXT(DATEVALUE(B2),"mmm")
In Column F you can use: =LEFT(C2,3)

Edit: Or you can use just =DATEVALUE(B2) and then format the cells as "mmm" in the custom format. The benefit is that you can sort by the actual date while only displaying the month, or if you don't care about that you can use the first example.

 

[MikeWx]

Witout columns E or F in Sheet 1, put the following formula in Sheet 2, Cell B2. Copy it to Cells B2 through F7.

=SUMPRODUCT(--(LEFT(Sheet1!$C$2:$C$15,3)=LEFT(UPPER(Sheet2!$A2),3)),--(MID(Sheet1!$B$2:$B$15,4,3)=TEXT(Sheet2!B$1,"mmm")),Sheet1!$D$2:$D$15)

As with your helper columns, this solution's logic has a problem if more than one Supplier has the same first three characters in their name.

 

[Papi]

Witout columns E or F in Sheet 1, put the following formula in Sheet 2, Cell B2. Copy it to Cells B2 through F7.

=SUMPRODUCT(--(LEFT(Sheet1!$C$2:$C$15,3)=LEFT(UPPER(Sheet2!$A2),3)),--(MID(Sheet1!$B$2:$B$15,4,3)=TEXT(Sheet2!B$1,"mmm")),Sheet1!$D$2:$D$15)

As with your helper columns, this solution's logic has a problem if more than one Supplier has the same first three characters in their name.

I did not show the date correctly in sheet1 as it is entered 05/30/2011 (not 30 May 11) and yet the Text in row 1 of sheet2 is Text as it shows i.e. May Jun etc. I tried changing it to LEFT instead of MID but cannot seem to make it work. Sumproduct will likely work but the rows change daily and can be a matter of a few rows into several hundred rows. We can control the 3 digit supplier code as those codes are internal and the suppliers are few.

 

[Papi]

One thing I noted was that if I press the F2 key and then press the enter key on each cell of Column B in sheet1 that the text date converts to an actual date and allows the first formula using sumifs to work. Is there a way that either a macro or some form of code could go into all of column B and change the dates from text into actual dates? The range could be a few rows or several hundred rows!

 

[CWatts]

This simple loop will do the trick.

Highlight the cells you want to change and execute the macro.

Sub DateChange()
For Each c In Selection
    If c.Value <> "" Then c.Value = DateValue(c.Value)
Next c
End Sub

Or specify a specific range if you don't want to bother with selecting.

Sub DateChange()
For Each c In Range("B2:B1000")
    If c.Value <> "" Then c.Value = DateValue(c.Value)
Next c
End Sub

 

[MikeWx]

Change the formula for Cell B2 of Sheet 2 to the following, to account for the dates in sheet 1:

=SUMPRODUCT(--(LEFT(Sheet1!$C$2:$C$15,3)=LEFT(UPPER(Sheet2!$A2),3)),--(TEXT(Sheet1!$B$2:$B$15,"mmm")=Sheet2!B$1),Sheet1!$D$2:$D$15)

 

[Papi]

Many thanks to both of you for your help on this matter. both of your ideas have been incorporated into not only this workbook, but a couple others as well.