Conditional Copy in Excel

Microsoft Excel 2007 home and student
October 22, 2009 at 12:29:15
Specs: Windows XP
Hello I am a beginner needing some help. I do not use VBA and would prefer not to do to my ignorance. But do not think I can accomplish what I would like to without it.

On Sheet 1 I have all my Data, on Sheet 2, I would like to pull names from B3:B500 if it meets two criteria listed under columns C3:500 and D3:500.

So for instance is B5 is Sam Roberts and C5 and D5 meet my criteria I would like Sam Roberts copied to Sheet 2 automatically.

I guess this could be accomplished through =IF and stating the two criteria on Sheet 2 but I think that way I'd have to have the formulas listed for A3:Z500 on Sheet 2 to work. Which I don't want. All I want on Sheet 2 is the copied name from Sheet 1 only if it matches the criteria I have selected.

I hope that makes sense, a beginners blabbering.

See More: Conditional Copy in Excel

Report •

October 22, 2009 at 12:42:00
Are you saying that you don't want:

=IF(AND(C5="Criteria 1",D5= "Criteria 2"),B5,"")

in a cell, but you want that cell to display Sam Roberts if the 2 conditions are true?

The only way you are going to get that is with VBA.

Why don't you want to use the formula?

Report •

October 22, 2009 at 13:14:47
Its not that I don't want to use the formalu, in fact I would prefer it, I just don't think I can get exactly what I'm trying to accomplish via a formalu.

If the formula you suggested worked like so:
=IF(AND(C5:500="Criteria 1",D5:D500= "Criteria 2"),*then copied B?, which met the Criteria 1&2*,"")

That would work but I would still have to figure out how to get the following cell on Sheet 2 to do the same search and ignore then name already pulled in the previous cell. That requires offsetting etc. that I think can only be done via VBA.

Essentially, what I want is all of Sheet 1 checked for two criteria, specifically "Current" listed under the C column and "Shannon" listed under the D column. Then if a certain row has both "Current" in C and "Shannon" in D then the name under column B will be copied to Sheet 2 under a column labeled Shannon. So sheet 2 should look like:

Column 1

Row 1 Sam Roberts
Row 2 Next Name that Meets Criteria 1&2 from Sheet 1
Row 3 Next Name that Meets Criteria 1&2 from Sheet 1
Row 4 Next Name that Meets Criteria 1&2 from Sheet 1
Row 5 Next Name that Meets Criteria 1&2 from Sheet 1
Row 6 Next Name that Meets Criteria 1&2 from Sheet 1
Row 7 Next Name that Meets Criteria 1&2 from Sheet 1

Hope that makes sense...

Report •

October 22, 2009 at 14:57:43

You can do the list you want with formulas.

Here is my sample database. It starts in column C, because you have to use columns A and B.
For this example the results are on the same worksheet as the database, but the results can easily be moved to a different sheet.

	C		D		E
1	Name		Criteria 1	Criteria 2
2	Sam Roberts	Male		Tall
3	Jill Roberts	Female		Tall
4	Arthur Buck	Male		Short
5	Tina Buck	Female		Short
6	Joan Armistice	Female		Tall
7	Bill Pertwee	Male		Tall
8	Billy Downturn	Male		Tall
9	Emily Banker	Female		Short

Create Criteria
In cells G1 to H2

	G		H
1	Criteria 1	Criteria 2
2	Male		Tall

(If your criteria never change, you can hard code the two values into the formulas in column B)

In Column B
Put text 'Match' in B1
In cell B2 enter:=IF(AND(D2=$G$2,E2=$H$2),1,"")
Note the $ signs
Drag the formula down alongside all names

In Column A
Put text 'Count' in A1
In cell A2 enter: =IF(B2<>"",SUM($B$2:B2),"")
Note the $ signs.
Drag the formula down alongside the names

Results are in columns I and J
Put Text 'Count' in I1
Starting in I2 enter numbers starting at 1 for however many matching results you expect.
Put Text 'Matching names' in J1
In J2 enter: =IF(ISNA(VLOOKUP(I2,$A$2:$C$9,3,FALSE)),
" ",VLOOKUP(I2,$A$2:$C$9,3,FALSE))
As always, note the $ signs
The formula has been split onto two lines for ease of viewing.

Drag the formula down as many rows as you entered numbers in column I

Here is the result table:

Count	Matching names
1	Sam Roberts
2	Bill Pertwee
3	Billy Downturn

How this works
Column B has a 1 whenever the entry matches the two criteria
Column A uses the 1's in column B to create a sequential value or counter for each matching entry

In column J, the sequential numbers in column I are looked up in column A
and the name with that value is returned.


Report •

Related Solutions

October 22, 2009 at 16:34:24
That was awesome...exactly what I wanted without VBA. Thank you, very much. First time I have posed a question to a forum, very impressed with the result. Thank you. Will take me a little time to apply your formula to my set but I feel pretty sure it will work.

Report •

October 23, 2009 at 04:30:11
You're very welcome.
I suggest that you start with the formulas in columns A & B, then when they work, move on to the results section.

I just noticed that I didn't include columns A & B in my response, so here they are with the names in column C.

A		B		C
Seq.#		Match		Name
1		1		Sam Roberts
				Jill Roberts
				Arthur Buck
				Tina Buck
				Joan Armistice
2		1		Bill Pertwee
3		1		Billy Downturn
				Emily Banker

If you have difficulties getting it going, please post again, and include formulas you have used, as this may help to diagnose the problem.


Report •

October 23, 2009 at 10:35:16
So what I did was pasted links from my names and my two criteria columns on my main data sheet to a separate sheet to columns C&D&E. Then I applied your formula almost exactly and it worked perfect. I then posted the results to a third sheet and it looks nice and gives me exactly what I want. Now I want to do the same thing with a different criteria, "Marla" vs. "Shannon" and without re-copying my three columns again and paste linking them again. Is there a way to change the formula so that it will work even if is is not directly adjacent to columns C&D&E? For Instance, below works if looking for Males that are Tall. Now I want to look for Females that are Short. Where do you plug that in? I think its just a tweak to the formula:
" ",VLOOKUP(I2,$A$2:$C$9,3,FALSE))

that you gave me for the J column?

Seq# Match# Name Criteria 1 Criteria 2
1 1 Sam Roberts Male Tall
Jill Roberts Female Tall
Arthur Buck Male Short
Tina Buck Female Short
Joan Armistice Female Tall
2 1 Bill Pertwee Male Tall

Report •

October 23, 2009 at 11:13:13

The way that this works is based on the results in columns A & B, to the left of the names.

Because of that you can't apply different criteria at the same time.

You could make the two criteria cells into drop-down lists and select new criteria.

Make two lists - one for all possible Criteria 1's and one for all possible Criteria 2's then use data validation on the two criteria cells (G2 & H2).

Data validation :
Select cell G2. From the toolbar -Data - Validation and select Allow - List.
Then enter the range with the values for Criteria 1.
Now G2 has a drop-down list that you can select from.

If you need two or more selections at the same time then make a copy of the original database using =C1, =D1, =E1 etc., so that you only need to enter data once in the original database cells.

Unfortunately you will have to duplicate the formulas in columns A & B alongside the copied (linked) data, and use two new criteria.

The result table can be anywhere, just make the ranges in the Vlookup formula point to the new copied data and adjacent column A.

If for instance your copy started at cells C101, then C101 is =C1, D101 is =D1 and E101 is =E1, then drag them down,
The formulas in columns A & B can be copied and pasted.

The results table starts with
" ",VLOOKUP(I2,$A$102:$C$109,3,FALSE))

I2 in the formula should adjust automatically and for the first formula will point to a cell containing 1 immediately to its left.

Hope this makes some sense! Anyway just keep asking.

PS if you use the pre tag icon above the response box you can put your data between the <pre> and </pre> tags and it will, more or less, retain the formatting in columns.

Report •

October 23, 2009 at 11:43:07

I haven't been following this thread so I'm just going to toss this out.

A while back you and I were involved with a multiple-criteria VLOOKUP where we suggested concatenating the 2 criteria in a separate list and then doing the VLOOKUP on that list.

Would something like that work in this situation? Maybe putting the concatenated list in a hidden column as the first column of the lookup_array and pulling data in that manner?

Just a thought.

Report •

October 23, 2009 at 19:06:48
Hi DerbyDad03,

I was looking for a way to do this without extra columns, but so far no joy.

I ended up using a technique I've used before to get results on sequential lines without gaps, but it needs two columns for counters.

I would really like to get this into one column, and have been looking at an array formula solution, but so far no joy.

Do you have the link to the post you mentioned, as it would help remind me of the other solution.


Report •

October 24, 2009 at 09:01:20

Here is a variation on my original suggestion, which makes it easier to get multiple selections from a single database.

A. Worksheet with two tabs: 'Database' and 'Results'
On the Database sheet:
1. One database of names and criteria

2. A sequence table with all required combinations of criteria, and calculating the required sequence numbers:
with three components to the table -
a. A selection number in the first row
b. Criteria in rows 2 and 3
c. Sequence numbers starting in row 4

3. A linked copy of the names to the immediate right of the sequence table

On the Results sheet
1. A table with criteria headings and results for each criteria combination

Database worksheet
Start the database of names with the first name in Cell A4
the criteria for the the first name are in cells B4 and C4

	A		B		C
3	Name		Criteria 1	Criteria 2
4	Sam Roberts	Male		Tall
5	Jill Roberts	Female		Tall
6	Arthur Buck	Male		Short
7	Tina Buck	Female		Short
8	Joan Armistice	Female		Tall
9	Bill Pertwee	Male		Tall
10	Billy Downturn	Male		Tall
11	Emily Banker	Female		Short

The sequence table has three parts:
In cell G1 enter 1, in H1 2, I1 3 and J1 4
(For this example there are four possible combinations of criteria).

In cells G2 to J2 enter Criteria 1 values
In cells G3 to J3 enter Criteria 2 values
(see example below)

In cell G4 enter this formula:


Drag this formula down to G11
then select G4 to G11 and drag the formulas across to column J

Cell J11 should have the following in it:


In cell K4 enter =A4
Drag the formula down to K11
This will provide a linked copy of the names in the database

The table (with headings) looks like this:

	F		G	H	I	J	K
1	Selection #	1	2	3	4	
2	Criteria 1	Male	Female	Male	Female	
3	Criteria 2	Tall	Tall	Short	Short	Names
4			1				Sam Roberts
5				1			Jill Roberts
6					1		Arthur Buck
7						1	Tina Buck
8				2			Joan Armistice
9			2				Bill Pertwee
10			3				Billy Downturn
11						2	Emily Banker

In the Results worksheet:

In cells B3 to B9 enter the numbers 1 through 7
In cell C2 enter the following formula:

=Database!G2 & ", " &Database!G3

Drag the formula across to cell F2
This gives a descriptive selection heading for each result.
This heading is not part of the calculations, so can be changed to suit.

In cell C3 enter the following formula:

=IF(ISNA(VLOOKUP($B3,Database!G$4:$K$20,6-Database!G$1,FALSE))," ",

Note: I have split the formula onto two lines for ease of viewing.

Drag this formula down to C9 then drag C3 to C9 across to column F
Cell F9 should have the following in it:

=IF(ISNA(VLOOKUP($B9,Database!J$4:$K$20,6-Database!J$1,FALSE))," ",

This is what the results table looks like:

	B	C		D		E		F
2	Count	Male, Tall	Female, Tall	Male, Short	Female, Short
3	1	Sam Roberts	Jill Roberts	Arthur Buck	Tina Buck
4	2	Bill Pertwee	Joan Armistice	 		Emily Banker
5	3	Billy Downturn	 	 	 
6	4	 	 	 	 
7	5	 	 	 	 
8	6	 	 	 	 
9	7	 	 	 	 

I didn't include any instructions for adding most of the headings to columns, but you can see what I did in the examples above.

The Columns in the results table can be moved independently, and could be on separate pages - but it was easier to create one formula and drag it across a single table.

The $ signs in formulas are important as they allow the formulas to extend properly when dragged in different directions.

The results formulas e.g., in cell C3 include 6-Database!G$1
This uses the selection numbers in the sequence table, so that VLOOKUP() knows how many columns to the right is the list of names.


Report •

October 25, 2009 at 06:14:47

I know that you did not want a solution that used visual basic (VBA), however, I have written a short user defined function that would do what you want without having to use any intermediate tables.

To use the function you enter this formula in a cell:
=Match2(data_table_range, Criteria_1, Criteria_2, Matching_Instance, Return_No_Error)

The data table is the range of cells containing the names and the criteria (3 columns wide)
Criteria 1 & 2 are the criteria to be met. These can be entered as text e.g. "Male","Tall" or as cell references.
Matching Instance is the nth match required. This can either be entered as a number 1, 2 , 3 etc or as a cell reference.
Return No error is a true or false value. If omitted it defaults to false.
If false, when there are fewer matches than requested the function returns the #NA error.
If true it returns an empty text string""
This saves having to test for errors, to avoid error values in a results table.

Here is an example of what the formula looks like with real values (formula on Results worksheet, data on Database worksheet range B4 to D11, the criteria are in cells C12 and C13 on the results page and there is a column of numbers starting at cell B14.)


To enter the UDF, from your workbook enter Alt + f11 (both the Alt key and the #11 function key at the same time), or Tools-Macro-Visual Basic editor.

This opens up the VBA Editor

In the left side Project Explorer pane, look for the name of your workbook (all workbooks are preceded by VBAProject.

Right click on the name and select Insert.
Insert Module (not class module)
A new module (Module1) will be added.
Double click on it.

Now in the main window paste the code,
then Save the project (File - Save)

Check that no lines of code are in red. If there are, there has been an error - let me know what line(s) are red.

Option Explicit

Public Function Match2( _
rngData As Range, _
strCr1 As String, _
strCr2 As String, _
intInst As Integer, _
Optional blnNoErr As Boolean = False _
) As Variant
Dim intRows As Integer
Dim rngOrigin As Range
Dim strErr As String
Dim intMatch As Integer
Dim n As Integer
On Error GoTo ErrHnd
If rngData.Columns.Count <> 3 Then strErr = xlErrRef: GoTo ErrEnd
Set rngOrigin = rngData.Cells(1, 1)
intRows = rngData.Rows.Count
intMatch = 0
For n = 0 To intRows
    If rngOrigin.Offset(n, 1).Text = strCr1 And rngOrigin.Offset(n, 2).Text = strCr2 Then
         intMatch = intMatch + 1
        If intMatch = intInst Then Match2 = rngOrigin.Offset(n, 0).Value: Exit For
    End If
Next n
If intMatch < intInst Then
    If blnNoErr Then
        Match2 = ""
        strErr = xlErrNA: GoTo ErrEnd
    End If
End If
Exit Function
Match2 = CVErr(strErr)
Exit Function
Match2 = CVErr(xlErrValue)
End Function

Go back to the Results worksheet and enter the formula with the ranges etc. as you would do for any other function.


Report •

October 26, 2009 at 10:16:45
Hello Humar,

Thank you for the time you put into this problem. Confused why you kept going, the intial solution you offered worked like a charm! I am reluctant to mess with it and try VBA. Is there any great advantage to VBA that I'm not getting vs. your earlier solution?

Also, seperate problem completely. Part of this database is a column for Referrants. I'd like to create a Referrent page that when a new referrant is entered on the Database that Referrant's name will be copied to the Referrent pages as well as the number of referrals of that Referrant.

I need their name copied and counted to see how many times it turns up the the Database. I have is set up now to count all the existing referrents. But, problem is everytime there is a new referrent I have to manually copy of their name and apply the Count formula to that individual. I will show what I would like below:

	Referrents:	                # Referred:
1	Roberts, Rob	2    
2	Roberts, Tom	3
3	Roberts, Lisa	8
4	Roberts, Benjamin	20
5	Roberts, Zachery	1

Thanks for all the help!

Report •

October 26, 2009 at 10:41:13

I wrote the UDF because it is likely to be an easier option to manage when changes are made to a database in the future.

Whilst I recognized that the formula approach worked, It was still a bit clumsy with the need for an intermediate table.

The UDF just needs a single formula on each result line, and is easier to follow (anyway that's my take on it), but I certainly have no intention of asking you to change - if what you have now works, that's great.

As to your new issue, could you post it as a new post.

The moderator for this forum has pointed out that it is easier to reference individual issues when they are in separate threads.

Also I think that it opens up your new problem to other users of the forum. When an issue is at the end of a long series of posts, people tend not to jump in.

When posting again, could you identify if there is one Referent per row in the database.


Report •

October 26, 2009 at 11:08:17
Copy, Posted to "Conditional Copy & Count in Excel." thanks!

Report •

Ask Question