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. Problem:

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.

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?

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

ShannonRow 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 1Hope that makes sense...

Hi, 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 ShortCreate Criteria

In cells G1 to H2G 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 namesIn 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 namesResults

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:

I J Count Matching names 1 Sam Roberts 2 Bill Pertwee 3 Billy Downturn 4 5 6 7How 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 entryIn column J, the sequential numbers in column I are looked up in column A

and the name with that value is returned.

Regards

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.

Hi,

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 BankerIf you have difficulties getting it going, please post again, and include formulas you have used, as this may help to diagnose the problem.

Regards

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:

=IF(ISNA(VLOOKUP(I2,$A$2:$C$9,3,FALSE)),

" ",VLOOKUP(I2,$A$2:$C$9,3,FALSE))that you gave me for the J column?

A B C D E

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

Hi, 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

sametime.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 timethen 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

=IF(ISNA(VLOOKUP(I2,$A$102:$C$109,3,FALSE)),

" ",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.

Regards

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.

Humar, 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_arrayand pulling data in that manner?Just a thought.

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.

Regards

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

Outline:

A. Worksheet with two tabs: 'Database' and 'Results'

On the Database sheet:

1. One database of names and criteria2. 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 43. 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 combinationDatabase 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 C4A 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 ShortThe 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:

=IF(SUMPRODUCT(($B4=G$2)*($C4=G$3))=1,1+MAX(G$3:G3),"")

Drag this formula down to G11

then select G4 to G11 and drag the formulas across to column JCell J11 should have the following in it:

=IF(SUMPRODUCT(($B11=J$2)*($C11=J$3))=1,1+MAX(J$3:J10),"")In cell K4 enter =A4

Drag the formula down to K11

This will provide a linked copy of the names in the databaseThe 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 BankerIn 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))," ", VLOOKUP($B3,Database!G$4:$K20,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))," ", VLOOKUP($B9,Database!J$4:$K26,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 7I 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.Regards

Hi, 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.)

=Match2(Database!$B$4:$D$11,C$12,C$13,$B14,TRUE)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 = "" Else strErr = xlErrNA: GoTo ErrEnd End If End If Exit Function ErrEnd: Match2 = CVErr(strErr) Exit Function ErrHnd: 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.Regards

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 1Thanks for all the help!

Hi, 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.

Regards

Copy, Posted to "Conditional Copy & Count in Excel." thanks!

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History