Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bttreadwell
 
Posts: n/a
Default how do I lookup data based on two columns of data

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default how do I lookup data based on two columns of data

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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default how do I lookup data based on two columns of data

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to lookup row # based on content of another column's cell Mr. Jan Park Excel Worksheet Functions 7 November 2nd 05 01:58 AM
extracting data from a spreadsheet by searching on columns Tom New Users to Excel 3 October 24th 05 10:36 PM
import data to specific columns marlea Excel Discussion (Misc queries) 1 August 12th 05 02:05 AM
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns foofoo Excel Discussion (Misc queries) 1 April 2nd 05 12:02 AM
How do i copy columns of data in notepad into microsoft excel? Jason Excel Discussion (Misc queries) 1 February 10th 05 10:20 PM


All times are GMT +1. The time now is 10:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"