PDA

View Full Version : LOOKUP?? how to find data (stuck on project..please help)



donyc1
03-23-2011, 10:41 AM
Hi everyone.. I have been trying to finish a project and been stuck on below part for some time.. I am looking for some help/advice.. Would be very much appreciated..

I have 2 tabs in a spreadsheet:

1) profits1 (has 3 columns(A, B, C) : account, id, profits) - contains 50 rows
2) profits2 (has 3 columns(A, B, C): account, id, profits) - contains 400 rows (50 rows contain profits already on data1.)

My profits are identified by 2 identifiers "account" and "id" ("id" is unique but it I have 5 static accounts so i can have a case of 5 same ids across 5 different accounts)

the problem where I am stuck:
I need to look remaining profits that are NOT on profits tab based per account/id and transfer them in the profit column(cell C3) on row 51 which then needs to be populated all the way down on data1.xls

Any help/advice would be greatly appreciated!

thanks so much

Wireless Guy
03-23-2011, 11:57 PM
It would help if you posted your Spreadsheet.

donyc1
03-24-2011, 07:49 AM
I have 2 spreadsheets.. i put detailed explanation inside the workbook.. Any help would be very appreciated! thanks so much

1) testing.xls (main spreadsheet)
2) this_month.xls (need to change dir name in testing.xls VBA wherever you save this file)

Wireless Guy
03-24-2011, 12:43 PM
I think I see what you're going for. I'll have a look at it later today. I think you probably actually just want a Pivot Table and report your results out of the pivot table.

I think you should just keep a running database (In Excel is fine) of all of the profit data, and then pull a pivot table off that data for current and previous month using the GetPivot command.

I'll look a little closer later tonight.

Wireless Guy
03-24-2011, 05:37 PM
Here you go. All I did was concatenate your data together into one table (Which I think you can do based on your code) and then add a month column. Next make a pivot table and you have what you're looking for.

Let me know if it works for you.

donyc1
03-29-2011, 02:23 PM
nice! this is good idea.. Thanks a lot.. I will work with it and see if I can finish implementing it