Calling multiple records to another sheet thru identical lookups

[habibian88]

i have the following data in an Excel Sheet. Starts A1..

e.g. in Cell A1 is the Date 04/08/2011 and B1 is the Time 13:39:47 and then A2 is the same date and in B2 is the time which is different.

04/08/2011 13:39:47
04/08/2011 17:30:23
05/08/2011 8:36:23
05/08/2011 9:46:46
05/08/2011 12:08:19
05/08/2011 12:30:56
06/08/2011 8:49:00
06/08/2011 9:54:54
06/08/2011 13:11:51
06/08/2011 17:15:44
08/08/2011 8:28:23

in another sheet of a different file starting from A1 i have the Dates already mentioned starting from 1st of the month till the last (total 31 days - every date is mentioned just once). i want to call the above data in this sheet..
e.g. there are two records of 04/08 and four of 05/08 in the above data..
Is there any formula thru which i can call this record horizontally against the date i have in that different file where i have mentioned date only once.. (records of each date can be different always...they can be 3, 4, 5 etc - i want data to come up against the date in File 2 irrespective of the number of records of each date of File 1)

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">2011/8/4</td><td style="text-align: right;;">13:39:47</td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/8/4</td><td style="text-align: right;;">13:39:47</td><td style="text-align: right;;">17:30:23</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2011/8/4</td><td style="text-align: right;;">17:30:23</td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/8/5</td><td style="text-align: right;;">8:36:23</td><td style="text-align: right;;">9:46:46</td><td style="text-align: right;;">12:08:19</td><td style="text-align: right;;">12:30:56</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2011/8/5</td><td style="text-align: right;;">8:36:23</td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/8/6</td><td style="text-align: right;;">8:49:00</td><td style="text-align: right;;">9:54:54</td><td style="text-align: right;;">13:11:51</td><td style="text-align: right;;">17:15:44</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2011/8/5</td><td style="text-align: right;;">9:46:46</td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/8/7</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2011/8/5</td><td style="text-align: right;;">12:08:19</td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/8/8</td><td style="text-align: right;;">8:28:23</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2011/8/5</td><td style="text-align: right;;">12:30:56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2011/8/6</td><td style="text-align: right;;">8:49:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2011/8/6</td><td style="text-align: right;;">9:54:54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2011/8/6</td><td style="text-align: right;;">13:11:51</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">2011/8/6</td><td style="text-align: right;;">17:15:44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">2011/8/8</td><td style="text-align: right;;">8:28:23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIF(<font color="Red">$A:$A,$D1</font>)>=COLUMN(<font color="Red">A1</font>),SMALL(<font color="Red">IF(<font color="Green">$A$1:$A$11=$D1,$B$1:$B$11</font>),COLUMN(<font color="Green">A1</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Copy across and down as needed (31 days or whatnot).

 

[habibian88]

Thanks dear for your immediate feedback..
Well i entered your Formula in E1 from the "=" (just before IF) till the Last Round Bracket (just before the Last Curly).. But the result of the formula is different.. It resulted "8:28:33" which i understand is the smallest of Entire Column B.

 

[habibian88]

Dear Sal,
that is working absolutely fine..i think i was missing something when yesterday i tried..many thanks again..
Regards,

 

[Sal Paradise]

Your data may be formatted in a different way. I entered the formula as shown in my example with the data as shown in my example and it worked. Are you sure you confirmed it with Ctrl-Alt-Enter?

 

[habibian88]

yeah i guess i missed to used Ctrl+Shift+Enter and was using just enter..well now it working great..

 

[Sal Paradise]

Please note, it will take a while to calculate the more rows/columns you have. If you go with thousands of rows and 100 columns, it could take minutes depending on your computer.

 

[habibian88]

Dear Sal,
One more question..for example i have a Trigger just after every Time Record and i want it to come up on the result screen just next to its relevant time record then what should i do..
Let me give a brief explanation of my question;


04/08/2011 13:39:47 IN
04/08/2011 17:30:23 OUT
05/08/2011 8:36:23 IN
05/08/2011 9:46:46 BANK-OUT
05/08/2011 12:08:19 BANK-IN
05/08/2011 12:30:56 OUT
06/08/2011 8:49:00 IN
06/08/2011 9:54:54 BANK-OUT
06/08/2011 13:11:51 BANK-IN
06/08/2011 17:15:44 OUT
08/08/2011 8:28:23 IN

I hope you are clear what I mean to ask.
Thank you!

 

[Aladin Akyurek]

Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">2011/8/4</TD><TD style="TEXT-ALIGN: right">13:39:47</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2011/8/4</TD><TD style="TEXT-ALIGN: right">13:39:47</TD><TD style="TEXT-ALIGN: right">17:30:23</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">2011/8/4</TD><TD style="TEXT-ALIGN: right">17:30:23</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2011/8/5</TD><TD style="TEXT-ALIGN: right">8:36:23</TD><TD style="TEXT-ALIGN: right">9:46:46</TD><TD style="TEXT-ALIGN: right">12:08:19</TD><TD style="TEXT-ALIGN: right">12:30:56</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2011/8/5</TD><TD style="TEXT-ALIGN: right">8:36:23</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2011/8/6</TD><TD style="TEXT-ALIGN: right">8:49:00</TD><TD style="TEXT-ALIGN: right">9:54:54</TD><TD style="TEXT-ALIGN: right">13:11:51</TD><TD style="TEXT-ALIGN: right">17:15:44</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2011/8/5</TD><TD style="TEXT-ALIGN: right">9:46:46</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2011/8/7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2011/8/5</TD><TD style="TEXT-ALIGN: right">12:08:19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2011/8/8</TD><TD style="TEXT-ALIGN: right">8:28:23</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">2011/8/5</TD><TD style="TEXT-ALIGN: right">12:30:56</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">2011/8/6</TD><TD style="TEXT-ALIGN: right">8:49:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">2011/8/6</TD><TD style="TEXT-ALIGN: right">9:54:54</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">2011/8/6</TD><TD style="TEXT-ALIGN: right">13:11:51</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">2011/8/6</TD><TD style="TEXT-ALIGN: right">17:15:44</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">2011/8/8</TD><TD style="TEXT-ALIGN: right">8:28:23</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">{=IF(COUNTIF($A:$A,$D1)>=COLUMN(A1),SMALL(IF($A$1:$A$11=$D1,$B$1:$B$11),COLUMN(A1)),"")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>


Copy across and down as needed (31 days or whatnot).

The expression COLUMN(A1) is risky for any column insertion in front of the current start column would change the results. It would be better to substitute

ROWS($E1:E1)

for

COLUMN(A1)

in the formula.

 

[Sal Paradise]

Aladdin, I do believe you mean COLUMNS($E1:E1), right?

ROWS($E1:E1) would always return 1 regardless of the column, until you paste it down in which case you get a wacky answer.