Finding "spend" in mulitiple columns

[rjc008]

I think I have the solution, but just need some help constructing a formula, also if this isn't the right track maybe you can lead to a better way. I have 42,000 rows 120 columns, 10 columns for each month. Each column represents a different product, what I'm looking to do is find out how many customers buy across several different products. For example:

Month 1
Prod 1 | Prod 2 | Prod 3 | Prod 4 |....
100 100 0 0

In this example you can see that this customer has purchased in two product categories; therefore, I would like a formula that would count the amount of columns that have spend over £100.

However, I don't know if this is possible but Ideally I'd like to know which categories they have purchased in. For example, if a customer who are primarily Product 1, what other products do they buy?

Am I on the right track for the first part, can someone help me with the formula, and can someone suggest something for the second part?

Thanks.

[Cheeky Charlie]

I would highly recommend moving your data into an (access or similar) database and running a pivot table from that (you don't need to import raw data to run a pivot table from it).

In terms of a formula, this is the right ball park:
=countif(<range>,">"&100)

The second part becomes almost trivial with a pivot table but you can't (I don't think) pivot the data from the layout you describe.

I would guess database programs might have tools for importing the data correctly, basically you need*:

Please Login or Register  to view this content.

to look like:

Please Login or Register  to view this content.

HTH

* need - in order to pivot the data, which is my recommendation to you, not need for everything - you may choose to soldier on...

[Cheeky Charlie]

A formulaic solution to the second question:

=INDEX(C$2:J$2,(MATCH(MAX(C3:J3),C3:J3,0)))

Will return the product with the largest value on the basis that:
C2:J2 are the product names and C3:J3 are the data.

You could replace max(C3:J3) with large(C3:J3, <then 1, 2, 3 etc.>) to return the second, third etc. largest orders

HTH

PS This does not change my recommendation to use a database program and a pivot table. My computer's not slow but this would kill it across the range you describe.

[Shijesh Kumar]

Hi rjc,

The senario which you mentioned, i think pivot will be best suited. It will help you to get error free report.

Using complicated formula will slow the system and also it is more prone to error.

I also work with huge amount of data as of your (50,000 and so rows).. and I found pivot to be a wonderful tool do analysis...

[rjc008]

Okay great, I'm starting to put everything in Access, but I'm trying to organise the revenue table I have and need some help with the pivot table in excel. My data is organised like

AccountNumber | 1 | 2 | 3 | ..... so on

a123 £ £ £

So I'm trying to put in the way you have it, but i'm having trouble. can you recomed how to put it in your format using the excel pivot table. The 1, 2, 3, are the Product codes.

Thanks.

[Cheeky Charlie]

You've got it the wrong way round...

The idea is that you put the data into this format in order to create a pivot table from it.

This shows a straightforward method, you'd need to split your table and run this a few times because the row numbers will become enormous. I do think it's worth it as a one-off task.

http://www.mrexcel.com/forum/showthread.php?t=342246

HTH

[rjc008]

Hi,

Thanks, I checked out the link. Unfortunately VBA is too advanced for me and I couldn't figure out how to change the code to make it work for me.

I there another method way to do it?

I have about 15,000 rows, each with a different accouts number A2:aA15000 and and 76 columns A1:BY. A1 is column heading for AccontNumber, B1 is month C1:BX1 is product code 1-74 with the final BY as the total.

[Cheeky Charlie]

Hey there, I saw your other thread.

Basically, the two formulae I provided will do what you've asked (badly) without VBA.

I would recommend rearranging the data for a pivot table, but I think the only way would be VBA. I understand how easy this suggestion isn't, but that's how much I think a pivot table would help...

My recommendation:
Start a new thread in programming, cross reference this and the link and gave you before and explain that you don't understand how to apply it to your data set.

As I think you know, rearranging the data is not really within the remit of this thread.

CC