Formula for copying over relevant data from one table to populate another table

**amasson** · 02-19-2013, 05:30 PM

Good Day Folks,

I am really between a rock and a hard place with this issue that I am having and was hoping that folks with far more knowledge than I can assist with. I am not bad in Excel but I am far from where I should be

So here is the issue:

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 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 1"
So - what is required is for a formula to look at the information in the DATA tab. It should look at Column D to determine what the type is. If it is a "Quote" type it then needs to look at Column V to determine if there is a NO in this field. If it is a QUOTE and it is also a NO - then it must copy over all the blue highlighted columns to the tab which I called "Needs to populate this".

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

Thank you in advance.

Take care,
Andrew

**dredwolf** · 02-19-2013, 06:36 PM

This ARRAY FORMULA (Cntrl+Shift+Enter,not just Enter) in A2:

Formula:

Please Login or Register  to view this content.

Copied down will give you the WO#

This can be used for the other columns as well by changing the red range above...AND it could be 1 formula for all columns if the Column headers were the same on the 'Needs to populate this'sheet as the 'data'sheet, as it is not, you'll have to change the columns for each category...

Hope this helps

Edit-
I would offer you the complete solution, but I cannot tell which columns go where with the sample you uploaded, that is why I only gave you the solution for the first column...sorry

**amasson** · 02-20-2013, 06:52 PM

Hi Dredwolf,

Thank you for the excellent response. Damn - I could never write a formula like you!!! It is brilliant. Can I just ask the following please:
1. It looks like I will need to copy the formula to each column / row but may I just ask if there is any risk of the data not being matched to the Work Order number? The first column is fine as it will be one set of information but will all the other cells on that line "always" match up?

Thank you for your kind consideration.

Take care,
Andrew

**dredwolf** · 02-20-2013, 11:15 PM

If you copy the formula in A2 to each of the other columns, AND change the red highlighted Data!$R$1 in the fomula, you will be able to copy them all down, the referencing for which item will be shown in which row will be consistent, so what's associated with the Work Order number is what will be displayed in the following columns
The actual finding of the row is separete from the column that the formula is in, so as long as the references are proper, the data will match up properly

Also, you stated 172,000+rows, so the references to $2602 (which is the last row of your sample) should be adjusted to your real data

(NOTE) when running on your real data, this may take a while, there are a LOT of calculations going on...

Hope this helps

**amasson** · 02-21-2013, 07:32 AM

Hi Dredwolf,

I have copied the formula across and it works a treat - I am amazed - so thank you.

May I add a further request please?
1. I forgot to mention that the formula needs to include a check on the Client (Column AF). The formula is going to be copied to two clients - and therefore one set will be for Client A and the other set for Client B. I tried to play around with the formula but it is not playing ball

Thank you for your help again.

Kindest regards
Andrew

**amasson** · 02-21-2013, 08:37 AM

Hi Dredwolf,

Further to the above statement - if I wanted to only display the last 50 or so recent entries - should this be done through simple table filters or should it be added to the formula?

Take care,
Andrew

**dredwolf** · 02-21-2013, 11:11 AM

Okay to make things easier, Put Client A or Client B into cell N1 and The Number 50 Into cell P1 on the 'needs to populate this' sheet, Then Change the Formula to this:

Formula:

Please Login or Register  to view this content.

The blue section is what I added for the Client check,And the Green is what I added as the count check
(Cntrl+Shift+Enter)
Then the same as before, drag across, change the Column reference, drag down

Hope this helps

**amasson** · 02-22-2013, 11:19 AM

Hi,

Thank you for this - much appreciated. Try as I might I am just not getting anything to show in the cells if I use the above formula - am I doing anything wrong? I have added the required values in Cell N1 and P1 and also use Ctrl+Shift+Enter

Take care,
Andrew

**dredwolf** · 02-22-2013, 02:11 PM

Not sure whats wrong, it works here, I'll upload the file so you can see it
In N1, I made a DV drop down to ensure spelling was right, might be something to check..
Hope this helps

**amasson** · 02-25-2013, 09:34 AM

You are a damn GENIUS. It works perfectly. Thank you sir.

If I have any amendments - could I still ask you or should I raise another post?

Take care.
Andrew

**dredwolf** · 02-25-2013, 11:25 AM

If it is related to this same Question, keep posting here, if it's something completely different, you are probably better off to start a new Post, to get as many minds as possible working on it

**amasson** · 02-25-2013, 12:00 PM

Okay - that sounds good to me. Although in my experience you have the best mind! Thanks again for all of your help

**dredwolf** · 02-25-2013, 12:41 PM

Thank you for those kind words, although I'll be the first to admit that there are many,many far brighter members on this forum

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

Formula for copying over relevant data from one table to populate another table

LinkBack

Thread Tools

Rate This Thread

Display

Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Re: Formula for copying over relevant data from one table to populate another table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions