Will the FREQUENCY function work for this?

[sweetmetrics]

Happy Friday everyone. So, I am hitting my head on the wall trying to figure this out and could use some help because I need to save what little brain cells I have left to do the rest of my work today.

I have a data set that stores the date in YYYYMM format in one column and the sale amount of a transaction in another column. The data set can average tens of thousands of rows.

Data Set Example 1:
201104, 50.00
201104, 25.00
201105, 10.00
201105, 40.00
201105, 30.00
201106, 40.00

I need to process this into a new blank data set of 5 columns where each row is one month in the same format as above and the 4 columns to the right of the first column represent dollar ranges e.g. 0-25, 25-50, 50-75, 75+. The objective being a count of records that fall within a dollar range for the corresponding month.

Data Set Example 2 (Desired Finished Output):
Date, Range 1, Range 2, Range 3, Range 4
201104, 1, 0, 1, 0
201105, 1, 2, 0, 0
201106, 0, 1, 0, 0

I can do this fairly easily with the FREQUENCY function with a small data set, but I can't figure out how to do it very fast for the larger data set where I
can do a look up of the date in Data Set Example 2 and only count the corresponding date rows in Data Set Example 1 and then copy that down for all months in Data Set Example 2.

Does that make sense or have I already lost too many brain cells?

Is there a fast way to do this for a lot of records in Excel?

Sincerely,

Shilo

 

[Ron Coderre]

If you use a pivot table, Excel will easily do the "heavy lifting" for you.
• Put the YearMth values in Row Headings
• Put the amounts in Column Headings
• Put the amounts in the Data section
...Change the formula from SUM to COUNT
• Click FINISH (depending on your version of Excel)

• Right-click the amount dropdown in the pivot table
...Select:Grouping
...Starting at: 0
...Ending at: 100
...By: 25
...Click: OK

• Right-click the pivot table...Pivot Table Options...For empty cells show: 0
• In Excel 2007 and later, you can specify displaying unused categories

Is that something you can work with?

 

[sweetmetrics]

Ron, thanks for your reply. There were a couple of issues that I ran into when I was trying out this solution. First, I am sorry that I forgot to say that I am running Excel 2010.

When I try and drop the amounts into the Column Labels I get the error that there are you can't place more than 16,384 into the column area.

I've never used grouping before in Pivot Tables so I guess I need to learn that, because it sounds like it could work. The problem I was running in to is that when I try and select anything to get the Grouping option it keeps saying you can't group that selection.

I'll try digging around some more, but if you have any thoughts on what I might be doing wrong, would love to hear from you.

Thanks,

Shilo

 

[Ron Coderre]

I use Excel 2010, too.
Is there any way you can put a mock-up of your workbook in your skydrive or one of the free file sharing services so we can take a look at it?
Without recommending any specific site, here are some free filehosts that could be used:
MediaFire: http://www.mediafire.com/
FileFactory: http://www.filefactory.com/
FileSavr: http://www.filesavr.com/
FileDropper: http://www.filedropper.com/

 

[sweetmetrics]

Absolutely, because I am stumped, but that isn't too difficult.

Here is a sample workbook. There are a lot of records in this data set so it gives you an idea of how painful it would be to try and do it by hand, but you probably could already imagine that.

http://www.mediafire.com/?z3dntn1dnwe816p

Thanks for any direction. It is very much appreciated.

Shilo

 

[Linebacker2]

Try this formula in B2 of set2:

=COUNTIFS('set1'!$A:$A,$A2,'set1'!$B:$B,">"&LEFT(B$1, FIND("-", B$1)-1),'set1'!$B:$B,"<="&RIGHT(B$1,LEN(B$1)-FIND("-", B$1)) )

You will need to modify it slightly for the final column, but otherwise it works.

 

[delaneyjm]

I got the same message as you did about 16K rows when creating the Pivot Table. However, if you put it in the row field, create the grouping, you can put it in the column field of the Pivot Table.

 

[sweetmetrics]

It never ceases to amaze me at what an incredible community of people participate in this forum. Both of the solutions worked great. Ron, delaneyjm and Linebacker2, thank you so much for your teaching me some new tricks, pointing me in the right direction and allowing me the opportunity to finish up my work today so I can go get some sleep.

Thanks,

Shilo

 

[sweetmetrics]

Oh BTW, Linebacker2, the way that I was able to get the final column working with the formula that you wrote but don't even really understand yet is to search for the MAX value in the array and then I just set the final column with a range that was greater than that. I assume that is a fine way to do it.