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.

For 401

=SUMPRODUCT(--(Sheet1!$A$1:$A$200=401),--(Sheet1!$B$1:$B$200=B$1),--(Sheet1!
$C$1:$C$200=$A2),Sheet1!$D$1:$D$200)

and so on.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"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.

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.