• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract data into another sheet in Excel 2007?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear All,

I have an excel sheet in which i have created 3 sheets named " DD which contains drop down to select date, Data which contains some transactions & Extract were the needed data is to be extracted. I need to extract only the date, TC, NET (P/L) & EC from the "Data" to the "Extract" sheet.

How this can be done. Do help me some of the experts out here.

Thanks & Regards,

Sonjoe

Working file attached.
 

Attachments

  • WS Extract Data.xlsx
    10.7 KB · Views: 5
i have used an Array formula

=IFERROR(IF(ROWS(Data!A$3:A3)>=DD!$C$3,"",INDEX(Data!A$3:A$100,SMALL(IF((Data!$A$3:$A$100>=DD!$C$3)*(Data!$A$3:$A$100<=DD!$C$4),ROW(Data!A$3:A$100)-ROW(Data!$A$3)+1),ROWS(A$3:A3)))),"")
use control-shift-enter
to get {} around the formula

NOT sure what version you are using - as with 365 , probably FILTER would be better

Also Aggregate maybe possible

What version of Excel are you using

if a later version like 365 - then filter
=FILTER(Data!A:A,(Data!$A:$A>=DD!$C$3)*(Data!$A:$A<=DD!$C$4),"No data")
 

Attachments

  • WS Extract Data-ETAF.xlsx
    17.8 KB · Views: 7
p45cal said:
he's using Excel 2007.
Click to expand...
!!!!! oh dear , missed completely
Not sure if my formula with index etc , works in 2007 - should do , i'm sure i was using 2007 when it first came out , till 2010 and for some companies longer

do you know if my formula will work in 2007 or have a better one

otherwise , I guess will wait to see what the OP has to say

Aggregate wont work as thats > = 2010
Filter is 365 , maybe 2019 , maybe a later non subscription service
 
ETAF said:
i have used an Array formula

=IFERROR(IF(ROWS(Data!A$3:A3)>=DD!$C$3,"",INDEX(Data!A$3:A$100,SMALL(IF((Data!$A$3:$A$100>=DD!$C$3)*(Data!$A$3:$A$100<=DD!$C$4),ROW(Data!A$3:A$100)-ROW(Data!$A$3)+1),ROWS(A$3:A3)))),"")
use control-shift-enter
to get {} around the formula

NOT sure what version you are using - as with 365 , probably FILTER would be better

Also Aggregate maybe possible

What version of Excel are you using

if a later version like 365 - then filter
=FILTER(Data!A:A,(Data!$A:$A>=DD!$C$3)*(Data!$A:$A<=DD!$C$4),"No data")
Click to expand...
I think the IF function is not required, as using IFERROR function already.

So, being a copy right and down formula

Try to modify it to,

In A3, CSE formula copied right to D3 and all copied down:

=IFERROR(INDEX(Data!$A$3:$M$100,SMALL(IF((Data!$A$3:$A$100>=DD!$C$3)*(Data!$A$3:$A$100<=DD!$C$4),ROW(Data!$A$3:$A$100)-ROW(Data!$A$3)+1),ROWS($A$3:$A3)),MATCH(A$2,Data!$A$2:$M$2,0)),"")
 
Since @SONJOE JOSEPH wanted only a subset of 4 columns he can try this array-entered formula into a range 4 columns wide and as many rows as there are rows in his Date named range:
Code: 
=IFERROR(INDEX(OFFSET(Date,0,0,,13),SMALL(IF((Date>=DD!$C$3)*(Date<=DD!$C$4),ROW(Date)-INDEX(ROW(Date),1)+1),ROW(Date)-INDEX(ROW(Date),1)+1),{1,10,11,13}),"")
This formula uses only that named range as the base of its output.
Array-entered formulae can be a pain when you have to use them in earlier versions of Excel; trying to overwrite or amend them can be a nuisance because you have to know how big the array is first. You can't delete/update part of an array.

I've used OFFSET which is a volatile function but I'm hoping it won't be too resource-hungry because it's a single formula for the entire array of results - fingers crossed.

A very short macro will:
  • clear everything below the intended destination to the bottom of the sheet, then
  • insert the array-entered formula in as big a range as necessary to accommodate a full set of results.
Code: 
Sub blah()
With Sheets("Extract").Range("A3")    'top left cell of destination range, you choose where.
  .Resize(Rows.Count - .Row + 1, 4).ClearContents
  .Resize(Range("Date").Rows.Count, 4).FormulaArray = "=IFERROR(INDEX(OFFSET(Date,0,0,,13),SMALL(IF((Date>=DD!R3C3)*(Date<=DD!R4C3),ROW(Date)-INDEX(ROW(Date),1)+1),ROW(Date)-INDEX(ROW(Date),1)+1),{1,10,11,13}),"""")"
End With
End Sub
 
ETAF said:
i have used an Array formula

=IFERROR(IF(ROWS(Data!A$3:A3)>=DD!$C$3,"",INDEX(Data!A$3:A$100,SMALL(IF((Data!$A$3:$A$100>=DD!$C$3)*(Data!$A$3:$A$100<=DD!$C$4),ROW(Data!A$3:A$100)-ROW(Data!$A$3)+1),ROWS(A$3:A3)))),"")
use control-shift-enter
to get {} around the formula

NOT sure what version you are using - as with 365 , probably FILTER would be better

Also Aggregate maybe possible

What version of Excel are you using

if a later version like 365 - then filter
=FILTER(Data!A:A,(Data!$A:$A>=DD!$C$3)*(Data!$A:$A<=DD!$C$4),"No data")
Click to expand...


ETAF you array formula is working perfectly fine.. Just checked it now. Thank you for helping me
 
ETAF said:
!!!!! oh dear , missed completely
Not sure if my formula with index etc , works in 2007 - should do , i'm sure i was using 2007 when it first came out , till 2010 and for some companies longer

do you know if my formula will work in 2007 or have a better one

otherwise , I guess will wait to see what the OP has to say

Aggregate wont work as thats > = 2010
Filter is 365 , maybe 2019 , maybe a later non subscription service
Click to expand...
Its working my dear
 
bosco_yip said:
I think the IF function is not required, as using IFERROR function already.

So, being a copy right and down formula

Try to modify it to,

In A3, CSE formula copied right to D3 and all copied down:

=IFERROR(INDEX(Data!$A$3:$M$100,SMALL(IF((Data!$A$3:$A$100>=DD!$C$3)*(Data!$A$3:$A$100<=DD!$C$4),ROW(Data!$A$3:$A$100)-ROW(Data!$A$3)+1),ROWS($A$3:$A3)),MATCH(A$2,Data!$A$2:$M$2,0)),"")
Click to expand...
Thank You Bosco for helping me
 
bosco_yip said:
Filter function is in Office 365, Excel 2021

or, Excel 2019 professional version only.
Click to expand...

Bosco this office 365 is very costly this guys are charging almost Rs.6000 per year. Is Microsoft Business Basic is good. but its only Rs.125 a month but i think it has limited features. My use is to link my trading terminal to excel and to get live data's. I also need to do lots of mathematical calculatins. Can u suggest some which can be affordable to me

Regards,

Sonjoe
 
you are welcome

I think,

Microsoft Business Basic, is (Uk = £4.50 per month per user)
  • Web and mobile versions of Office apps
So does not include desktop versions which run on the PC

Do you need Business versions - Home cost in UK is
Microsoft 365 Personal £59.99 per year
 
Last edited:
ETAF said:
you are welcome

I think,

Microsoft Business Basic, is (Uk = £4.50 per month per user)
  • Web and mobile versions of Office apps
So does not include desktop versions which run on the PC

Do you need Business versions - Home cost in UK is
Microsoft 365 Personal £59.99 per year
Click to expand...

It comes to around Rs.6000 bugs every year according to India. If this was a one time payment i would have been paid.
 
It’s cheaper because of the subscription
Plus all the time you pay you get later version with the new functions
But if 2007 does everything you need
Apart from security issues as it older and no longer supported
 
You must log in or register to reply here.
Back
Top