Posts by Robert Mika

Robert Mika · Aug 6th 2013

Re: Vlookup needs revision

Quote from Zpichette;678449

I have never used either of those functions i was hoping there was a way to make this work with just a vlookup...

H6 drag down and accross
=IFERROR(INDEX(Sheet2!$A$2:$AA$46312,MATCH($C6,Sheet2!$F$2:$F$46312,0),MATCH(H$5,Sheet2!$A$1:$AA$1,0)),"no match")
Copy from Sheet2 any of the header you want to rertive.
To example Copy/Paste from Sheet2 "Part Description" into H5 Sheet1.
[TABLE="width: 237"]

[tr]

[td][/td]

[/tr]

[/TABLE]

Robert Mika · Aug 6th 2013

Re: VLookup/Index to return multiple values

forum.ozgrid.com/index.php?attachment/55557/

Please see attached

Robert Mika · Aug 5th 2013

Re: extract data from a sheet if a cell meets a criteria and paste in another sheet

Quote from kaelo;678245

i have a sheet with data i want to extract only data in column A that is greater than 29999 but less than 50000 and paste in another sheet named extract in the same work book.
See sample data below

[TABLE="width: 741"]
[tr]

[TD="class: xl65, width: 94, bgcolor: transparent"]GL_CODE[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]GL_DESC[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]BRANCH_CODE[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]CURRENT_BAL_LCY[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]OPEN_BAL_LCY[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]DAY_VARIANCE[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]PREVIOUS_MONTH_BAL[/TD]
[TD="class: xl65, width: 108, bgcolor: transparent"]MONTH_VARIANCE[/TD]
[TD="class: xl65, width: 98, bgcolor: transparent"]FCY_Contribution[/TD]
[TD="class: xl65, width: 98, bgcolor: transparent"]LCY_Contribution[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]111100001[/TD]
[TD="class: xl63, bgcolor: transparent"]CASH - TILL[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-20,749,991.61[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-43,829,460.99[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]23,079,469.38[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-38,993,120,698.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38,972,370,707.15[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-13,976,349.61[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-6,773,642.00[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]111100001[/TD]
[TD="class: xl63, bgcolor: transparent"]CASH - TILL[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-11,320,483.28[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-7,973,334.25[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-3,347,149.03[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-38,993,120,698.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38,981,800,215.48[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-2,668,477.78[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-8,652,005.50[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]111100001[/TD]
[TD="class: xl63, bgcolor: transparent"]CASH - TILL[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-28,915,002.46[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-19,874,396.89[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-9,040,605.57[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-38,993,120,698.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38,964,205,696.30[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-5,409,442.46[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-23,505,560.00[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]111100001[/TD]
[TD="class: xl63, bgcolor: transparent"]CASH - TILL[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-52,725,649.89[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-62,635,969.32[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9,910,319.43[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-38,993,120,698.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38,940,395,048.87[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-14,814,959.89[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-37,910,690.00[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]322000004[/TD]
[TD="class: xl63, bgcolor: transparent"]COMMISSION[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]382[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12.86[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12.86[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]138,760,477.85[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-138,760,464.99[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12.86[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]322000004[/TD]
[TD="class: xl63, bgcolor: transparent"]COMMISSION[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]384[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]166,050.88[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]166,050.88[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]138,760,477.85[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-138,594,426.97[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]166,050.88[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]322000004[/TD]
[TD="class: xl63, bgcolor: transparent"]COMMISSION[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]388[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1,049,837.66[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1,049,837.66[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]138,760,477.85[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-137,710,640.19[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1,049,837.66[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]424990095[/TD]
[TD="class: xl63, bgcolor: transparent"]EXPENSE - OTHERS[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-193.31[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-193.31[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-91,304,583.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]91,304,390.45[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-193.31[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]424990095[/TD]
[TD="class: xl63, bgcolor: transparent"]EXPENSE - OTHERS[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]57[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-10,149.06[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-10,149.06[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-91,304,583.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]91,294,434.70[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-10,149.06[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]424990095[/TD]
[TD="class: xl63, bgcolor: transparent"]EXPENSE - OTHERS[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]58[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-91,304,583.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]91,304,583.76[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent, align: right"]424990095[/TD]
[TD="class: xl63, bgcolor: transparent"]EXPENSE - OTHERS[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-9,978.28[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-9,978.28[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-91,304,583.76[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]91,294,605.48[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]-9,978.28[/TD]
[/tr]

[/TABLE]

Display More

Your GL Code are 9 digits long so they will never be less/more that your criteria.