Require Formula to extract data to then populate multiple cells

Hi,

I am battling to sort this issue out and was dearly hoping that folks with far more knowledge than I can assist with please. The issue is quite involved and I am not sure that it is even possible.

SCENARIO:
I have a worksheet with 172,000 + rows and about 30 columns - called the DATA tab. I then add new worksheets that looks at the DATA tab and pulls out certain information from this tab to then create various tables and graphs - basically the aim of all of this is to create Financial reports. I have done most of the exercise but am now stuck on the following:

ISSUE:
See attachment: "Example Data"
So - what is required is for a formula to look at the information in the DATA tab. It should look at
1. Column H (WO Type) to determine what the type is. If it is a "Quote" type it then needs to,
2. Look at Column Z (Capital?) to determine if there is a YES in this field.
3. It then needs to look at Column AL (Division). It needs to look for Client 1
4. If it is a QUOTE and it is also a YES and it is CLIENT 1 - then it must copy over all the blue highlighted columns to the tab which I called "Needs to populate this".
5. The difficulty is also that one of the columns to be copied over, namely Column AJ (Status), needs to populate different columns based on the status – probably easier explained if you look at the table that it needs to populate.
6. In addition, as this table is copied over to Word automatically, there can only be a maximum of 40 rows per table. Is this possible?

I hope that this is enough information and that it is easy to achieve.

Thank you in advance or for letting me know if this is possible.

Take care,
Andrew

Let's give a try Andrew.

In A2, put this ARRAY formula and copy down. Same way for the other columns.

=INDEX(Data!$V$2:$V$10000;SMALL(IF((Data!$H$2:$H$10000="Quote")*(Data!$Z$2:$Z$10000="Yes")*(Data!V$2:$V$10000="Client 1");"";ROW(Data!$V$2:$V$10000)-1);ROW(Data!AJ1)))

You are right! My bad!

In A2 put this ARRAY(See my signature) Formula and copy down.

=IFERROR(INDEX(Data!$V$2:$V$10000,SMALL(IF((Data!$H$2:$H$10000="Quote")*(Data!$Z$2:$Z$10000="Yes")*(Data!$AL$2:$AL$10000="Client 1"),ROW(Data!$V$2:$V$10000)-1),ROW(Data!AJ1))),"")

This should gives you these results.

8969322
9071357
9110044
9197036
9353252
9394652
9495999
9526915
9582910
9631759

Aren't these correct?

Of course it's OK. Just i hope you have a really "strong" combuter to handle all these array formulas in a huge table..

Originally Posted by Fotis1991

Of course it's OK. Just i hope you have a really "strong" combuter to handle all these array formulas in a huge table..

Exactly as i told you....

I believe that you need VBA for this and maybe not even Excel for this... Access perhaps...

I hope some VBA quy will be able to helps you.

Good luck.

Originally Posted by amasson

Hi Fotis,

If I wanted to only show values for Work Orders that are in the following statuses: Assigned, Service Incomplete and Service Complete - where would I put this? This is column AJ. Currently it returns all values over and above these 3 ones. I tried to amend the formula but then it displayed all blanks.

Thanks again sir.
Andrew

As your worksheet has many many columns..i am not sure what exactly are you asking for...

Which formula do you try to use and where? Which is the expected results?

Also pls notice that as time is 20:00pm here, i have to leave from the office soon...

SO If it is a QUOTE and it is also a YES and it is CLIENT 1 -and AJ= "Service Complete" and "Assigned" and "Service Incomplete". ???

Probably i'll see your reply Monday morning...

Apologize for this, but i have to say that i forgot you...

Try this.

In AU2 of your Data Sheet, put this and copy down until the last row of data.

=IF(OR(AJ6045="Service Complete",AJ6045="Assigned",AJ6045="Service Incomlete"),1,"")

Then use this ARRAY formula.

=INDEX(Data!$AJ$2:$AJ$10000,SMALL(IF((Data!$H$2:$H$10000="Quote")*(Data!$Z$2:$Z$10000="Yes")*(Data!$AL$2:$AL$10000="Client 1")*(Data!$AU$2:$AU$10000=1),"",ROW(Data!$AJ$2:$AJ$10000)-1),ROW(Data!AJ1)))

Good morning Andrew.

As already(at least in 2 post) explained, Your workbook contains 200000 rows and using my suggestion you use many ARRAY formulas, that "kill" your computer. I don't believe that my suggestion is the best way to go...

I strongly believe that you need VBA for this.

My new formula in the helper column is not the one that create problem. It's a really simple formula and to copy down you don't need more than 1 minute...and have almost zer0 memory space...