Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in excel
I have Following Date in sheet1
Sheet1 ColA ColB ColC ColD Room ChqNo ChqDt Amt 1 11551 01-06-09 500 2 25516 01-07-09 300 3 55159 01-08-09 250 1 11552 01-07-09 500 4 88192 01-08-09 300 2 25517 01-08-09 200 3 55160 01-11-09 200 1 11553 01-11-09 200 3 55161 01-12-09 200 4 88192 01-12-06 200 In Sheet 2 I Have following Room Chq1 Chq2 Chq3 Chq4 Chq5 1 2 3 4 I need a formula to get the cheque amt in respective room Final result should look this way Room Chq1 Chq2 Chq3 Chq4 Chq5 1 500 500 300 0 0 2 300 200 0 0 0 3 250 200 200 0 0 4 300 200 0 0 0 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in excel
One play which delivers it ..
(disregarding some inconsistencies in your description due to typos) In Sheet1, In F2: =IF($A2="","",IF($A2=COLUMNS($A:A),ROW(),"")) Copy F2 across by 4 cols to I2 (since you have 4 rooms), then fill down as far as required to cover the max expected extent of source data In Sheet2, In B2: =IF(COLUMNS($A:A)COUNT(OFFSET(Sheet1!$E:$E,,ROWS( $1:1))),"",INDEX(Sheet1!$D:$D,SMALL(OFFSET(Sheet1! $E:$E,,ROWS($1:1)),COLUMNS($A:A)))) Copy B2 across/fill down as far as required to return the desired results, all neatly packed to the left. And if you want to extract the corresponding chq numbers (from col B in Sheet1), just make a copy of Sheet2, then change the INDEX part of the formula in B2 to point to Sheet1's col B instead, ie change to: ... INDEX(Sheet1!$B:$B, ... Then re-copy B2 across/fill down Above any good? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "claude jerry" wrote: I have Following Date in sheet1 Sheet1 ColA ColB ColC ColD Room ChqNo ChqDt Amt 1 11551 01-06-09 500 2 25516 01-07-09 300 3 55159 01-08-09 250 1 11552 01-07-09 500 4 88192 01-08-09 300 2 25517 01-08-09 200 3 55160 01-11-09 200 1 11553 01-11-09 200 3 55161 01-12-09 200 4 88192 01-12-06 200 In Sheet 2 I Have following Room Chq1 Chq2 Chq3 Chq4 Chq5 1 2 3 4 I need a formula to get the cheque amt in respective room Final result should look this way Room Chq1 Chq2 Chq3 Chq4 Chq5 1 500 500 300 0 0 2 300 200 0 0 0 3 250 200 200 0 0 4 300 200 0 0 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions |