Another option .. perhaps quite ideal to use a pivot table (PT)
(Takes only a few clicks & drag-n-drops to set-up)
Here's a sample construct:
http://cjoint.com/?lte0RUonJK
Pivot_Table_bttreadwell_wks.xls
Assume the source data is in cols A to D,
with labels in A1:D1, data from row2 down, viz.:
Key Mth Acc Val
401 1 50204 $200
401 1 50206 $200
401 2 50204 $200
etc
Select any cell within the table
Click Data Pivot Table Report
Click Next Next
In step3 of the wiz.:
Drag and drop "Key" within the ROW area
Drag and drop "Acc" within the ROW area, below "Key"
Double-click on "Key", select "None" for subtotals OK
Double-click on "Acc", select "None" for subtotals OK
Drag and drop "Mth" within the COLUMN area
Drag and drop "Val" within the DATA area
(It'll appear as "Sum of Val")
Click Finish
The PT will be created in a new sheet just to the left,
giving the summary you want
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bttreadwell" wrote in message
...
I have data in 4 columns, I want to look at two (or three if it works)
columns and if data matches, put 4th column data in that cell.
Ex.
Here is my source data.
A B C D
1 401 1 50204 $200
2 401 1 50206 $200
3 401 2 50204 $200
4 401 2 50206 $200
5 401 3 50204 $200
6 401 3 50206 $200
7 401 4 50204 $200
8 405 1 50204 $200
9 405 1 50206 $200
10 405 2 50204 $200
11 405 2 50206 $200
12 405 3 50204 $200
13 405 3 50206 $200
14 405 4 50204 $200
etc.
Column B refers to a month of the year.
I want to turn it into this type of format:
For 401
A B C D E
1 1 2 3 4
2 50206 $200 $200 $200 $200
3 50206 $200 $200 $200 $200
Row 1 refers to the same months of the year.
And then do the same thing for 402.
Is this possible??? I tried to use a lookup function, but I could not
figure out how to look for 2 variables and then if they both match the
result
be a third variable. It would be a simple cut and paste, but my source
data
is 50K+ lines long it covers 50 units (column A), 2500 account codes
(column
C), and the values for those respective code (column D).
Any help would be greatly appreciated.