Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Anna
 
Posts: n/a
Default How to pull numbers from two tables with conditions?

I have 2 tables with identical number of lines. Both tables pulling
information from other sheets and other tables. They are summaries. Both of
them can have the same items listed, but, again, they might not. IF the items
appear in any one of those tables, the identical ones will be in the same
order.
Now, I need to do a summary of these 2 tables as well, in a third (lets call
it Total) table.
Since I do not know which one of the two tables will have the item listed,
but if they are, I need to sum the quantity sold in both, in addition to
listing them.
Here is a breakdown:
Column A - code, to identify the item
Column D - item description
Column E - quantity sold.
Table 1 begins on line 20, Table 2 begins on line 40, Table Total begins on
line 100
I use the following: if($A200,D20,D40) and
if($A1000,E20+E40," ")
The problems I have a
1. The second formula that calculated quantity should work only if column A
pulled a code, but it works at all times instead
2. The first formula, that suppose to pull an item description, only if the
item's code listed in the first table, and if not, in the second, pulls only
from the first table. So, if there is nothing, it lists nothing.

I really hope you can help me! It's been very frastrating!!
Thank you in advance!

  #2   Report Post  
Alok
 
Posts: n/a
Default

Use this formula in E100
=sumif($A$1:$a$99,A100,$E$1:$E$99)
This will pull in the total sales from both tables as long as the A100 value
matches any value in A1:A99

Use this formula in D100
=VLOOKUP(A100,$A$1:$D$99,4,TRUE)
This will pull in the matching description from one of the tables.

Alok Joshi

"Anna" wrote:

I have 2 tables with identical number of lines. Both tables pulling
information from other sheets and other tables. They are summaries. Both of
them can have the same items listed, but, again, they might not. IF the items
appear in any one of those tables, the identical ones will be in the same
order.
Now, I need to do a summary of these 2 tables as well, in a third (lets call
it Total) table.
Since I do not know which one of the two tables will have the item listed,
but if they are, I need to sum the quantity sold in both, in addition to
listing them.
Here is a breakdown:
Column A - code, to identify the item
Column D - item description
Column E - quantity sold.
Table 1 begins on line 20, Table 2 begins on line 40, Table Total begins on
line 100
I use the following: if($A200,D20,D40) and
if($A1000,E20+E40," ")
The problems I have a
1. The second formula that calculated quantity should work only if column A
pulled a code, but it works at all times instead
2. The first formula, that suppose to pull an item description, only if the
item's code listed in the first table, and if not, in the second, pulls only
from the first table. So, if there is nothing, it lists nothing.

I really hope you can help me! It's been very frastrating!!
Thank you in advance!

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
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM
Count and Sum Total occurrances of two specific numbers Sam via OfficeKB.com Excel Worksheet Functions 10 March 29th 05 08:13 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Converting Numbers to Text properly Shirley Munro Excel Discussion (Misc queries) 1 February 16th 05 04:01 PM


All times are GMT +1. The time now is 11:40 PM.

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"