Vlookup,match/index,sumproduct ???? 1

[mudstuffin]

Hello. Sorry for the waffle you're about to read, but here goes...

I am trying to perform a lookup on a range of data, looking at 2 sets of criteria. I have a list of client reference numbers on sheet1, and on sheet2, I have a list of all the transactions for the clients with a transaction type code. I want to add four columns to the sheet one to lookup the referral codes in sheet2 to the client number for each row, and then flag it if it is one of four that I'm specifically trying to flag up, to then filter.

I.e.

Sheet1 would start like this...

Client 0001 0002 0003 0004
jim
bob
mike
sam


then with the transaction data in Sheet2 like this....

Client
jim 0001
jim 0002
mike 0096
sam 0003

Would give this on Sheet1

Client 0001 0002 0003 0004
jim 1 1
bob
mike
sam 1

(The 1 represents a match as opposed to a count.)

I have managed to do this with sumproduct, by adding a column to the sheet2, and using an if statement to say 'if(referral code cell)<>&quot;&quot;,1,0'. The trouble with this way is that it takes a long time, to run all the formulas (45000 transactions from 5000 clients). Is there a quicker and/or better way of doing this. It cannot be done in access, has to be excel....!

If this is confusing and you are willing to have a go, I can email the spreadsheet to you.

What I am trying to achieve is :- cross reference client no to transactions sheet and if has one of four referral codes against any of clients transactions,flag it in the client row in the client sheet. Or basically a vlookup using 2 criteria.

Regards,


mudstuffin

 

[SkipVought]

Hi,

Match, count who cares. The presence of a value is a match!

Use a Pivot Table, Quick 'n' easy!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mudstuffin]

Thanks Skip.

I'm not too hot on pivot tables, so wondered if you can help a bit further.

Will it be able to tell me if there has been an instance of any or all of the 4 particular referal codes in a row for each client. I dont need to know how many of each, just that there is a match. i.e.

client 0001 0002 0003 0004
jim 1 1

Thanks again,


mudstuffin.

 

[jdhilljr]

If you would like email the file to:
jim@procollectortips.com
If it is large please zip the file.

Jim

 

[SkipVought]

Here is the result of a pivot table on your data...

Count of Number	Number				
Client   1   2   3  96   Grand Total
jim      1   1                 2
mike	             1         1
sam              1             1

Select any cell in your data table

Data/Pivot table and pivot chart report

MS Excel list or database &



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

sorry hit the wrong gotton...

BTW, You need a heading for the second column -- I'm calling it Number,

MS Excel list or database & Pivot Table -- Next

Step 2 is the table range -- next

Step 3 - Layout -
Drag Client to ROW Area
Drag Number to COLUMN Area
Drag Number to DATA Ares and double click on Number icon in Data Area and select Summarize by Count.

OK, OK, Finish

If the PivotTable is NOT exactly the way you want, then right click in the Pivot Table and select Wizard and change some stuff, EXPERIMENT to DISCOVER what it can do.

A Pivot Table Report is a QUICK and EASY solution to many data analysis opportunities.






Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Quick, easy, beautiful, magical, wonderful...........

Needless to say I endorse Skip's comments to the hilt

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[mudstuffin]

Thanks Skip.

Works great. That's me converted to pivot tables...

Only one other thing, can I get the report to skip blank lines i.e clients with none of the referal codes? I know I can just apply a sort and/or filter, but wondered if there was something built in to do it as the report is produced/refreshed.

Thanks again.



mudstuffin.

 

[SkipVought]

Within the Pivot table you can select COLUMN or ROW fields and include/exclude data elements.

Glad you are on the Pivot Table Train!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com