Combining data from multiple tables

[tazz]

Hello,

I have 3 Tables:

"Main"

A B C D

1


2 Start Date:"B2"


3 End Date: "B3"


Table A

A B

1 111 "Date"

2 111 "Date"

3 222 "Date"


Table B

A B

1 111 "Date"

2 111 "Date"

3 333 "Date"

4 444 "Date"


I want row 1 in "Main" to take and show data in ascending order from Tables A & B only for a specific period of time selected in "Main".

Any ideas?

Thank you

 

[jason]

usually, a question will get a fairly fast response (depending on level of difficulty of request and if there the local excel ninjas are off battleing those daaaarrrn (see what i did there?!) pirates.


OR the original request is not clearly understood. I feel this is the case with your post. please try to give a more detail explanation or possibly upload a sample worksheet.

 

[tazz]

Hello,

In "Main" table in cell A1 should be for example 111, in cell B1 should be 222, in C1 should be 333 and so on only if the date attached to each number is in range B2 to B3(from Main)

Thank you

 

[NARAYANK991]

Hi ,


I have assumed that one table is from A2:B10 , with the values in column A , and the dates in column B.


I have assumed the other table is from D2:E10 , with the values in column D , and the dates in column E.


I have assumed two cells , StartDate and EndDate , contain the date limits.


The following formula , entered as an array formula , using CTRL SHIFT ENTER , will probably do the job :


=SMALL(IF(CHOOSE({1,2},$B$2:$B$10,$E$2:$E$10)>=StartDate,IF(CHOOSE({1,2},$B$2:$B$10,$E$2:$E$10)<=EndDate,CHOOSE({1,2},$A$2:$A$10,$D$2:$D$10))),ROW(A1))


Enter the above formula in any cell , and copy downwards.


Narayan

 

[tazz]

Hi,

The above formula brings the first correct number in the cell A1 but when it is copied into the next cells brings the same number instead of the next, and next ... number.

Any suggestions?

Thank you.

 

[NARAYANK991]

Hi ,


That is not possible ; if you have your data organized the way I have posted , and if you have copied + pasted the formula as it is , using CTRL SHIFT ENTER to enter it , then , when you copy it downwards , since the ROW(A1) will change to ROW(A2) , ROW(A3) and so on , the values which are retrieved cannot be the same , unless the input data has duplicates.


Can you recheck , and if the problem still persists , upload your sample file ?


Narayan

 

[tazz]

Hi,

I moved the first table to a new field B2:C10 because it was interfering with the formula and is working.

There is another bump in the road: the formula brings the SAME number as many times the date frame allows(1, 2 3 .. times)and I need every available number only once.

My mistake, I was not very clear from the beginning.


Thank you

 

[tazz]

Hi Narayank991,

I found out that the formula you put it here (thank you)has a small problem (it took me 2 day to discover it).

Formula is omitting the minimum value from the tables(for instance I went with only one value in each table and the min value was not return).

Any idea to fix it?

Than you

 

[NARAYANK991]

Hi ,


I cannot see how that is possible. Can you upload a sample workbook , showing this problem ?


Narayan

 

[tazz]

Hi,

For some reasons I am not able to upload any files from this computer.

I just discovered what is going on: formula is working only if i start with cell A1 down, but I need A1 for a header because without header I am not able to filter data for unique values.

As you can see I am going in circle.

When I applied formula starting with A2, as I said, it was skipping the min. value.

Any idea how to fix this situation?

Thank you.

 

[NARAYANK991]

Hi ,


As long as the ROW(A1) is the starting point , you should always get the smallest value first ; please note that this A1 does not mean you have to start with cell A1 down ; wherever you start from , even say A17 , the first formula should have ROW(A1) , and this when you copy it down , changes to ROW(A2) for the second smallest , ROW(A3) for the third smallest , and so on.


Can you copy paste the first few rows of data , and your top-most formula here ?


Narayan