If statements

[Graysleaf]

Good morning,

I would like to know how to write an IF statement that is capable to do the following:

=IF(C3:I8)(Sheet1) contains "5229" then copy A3 of same row(Sheet1) and paste to (Sheet4) B2

Any ideas?

 

[Graysleaf]

This worked great when I posted it to same sheet
I just need to figure some way to use more than one 5229 entry. Column A has a list of names. The array C3:I8 will contain 5229 in several locations. any location that has 5229 in array will need to pull the name in column A and move it to another sheet. If there are duplicate 5229 then I will have duplicate names and I'd like all the names to be in one cell of another sheet.
Thanks,

 

[Yongle]

I am sure that somone could create an ARRAY formula - too complex for me

The VBA however is very simple

Sub Test()
    For Each cel In Sheets("Sheet1").Range("C3:I8")
        If cel.Value = 5229 Then  Sheets("Sheet1").Range("A" & cel.Row).Copy Sheets("Sheet4").Cells(Rows.Count, "B").End(xlUp).Offset(1)
    Next
End Sub

It copies the values from sheet 1 column A of the same row as found value "5229" to the next available cell in Sheet 4 column B

 

[Yongle]

@Graysleaf
I have started a thread with a more relevant title asking others to look at my Array formula to see if they can modify it
I will monitor that thread to see if anyone replies there, but I have included a link to this thread
This is the link to other thread

 

[Aladin Akyurek]

Good morning,

I would like to know how to write an IF statement that is capable to do the following:

=IF(C3:I8)(Sheet1) contains "5229" then copy A3 of same row(Sheet1) and paste to (Sheet4) B2

Any ideas?

Care to post what you have in C3:I8 and A3:A8?

 

[Graysleaf]

Hi
C3:I8 contain cells that list a number. I've been using 5229 as an example. (several duplicates of numbers will be in this array but only one per cell)
A3:A8 contain peoples names. (no names are duplicated)
If C3:I8 contains 5229 then same row column A would be pasted on different sheet, say Sheet4 B2. If multiple names need to be placed in cell B2 could they appear as a list?

 

[Graysleaf]

A B C D
1 Jim 23 23 24
2 Kay 23 23 23
3 Pat 19 19 19
4 Ken 17 19 17

Id like to enter a number in array of B1:D17 as indicated "23"
When 23 is entered Id like the name "Jim" to be pasted to another sheet in a text box would be ideal so that I could move it around.
If any other 23's are in array B1:D17 then I'd like them to be in same text box.
column's A-D are calendar dates. Id like the "text box" to only be looking at current day of the week.

If you guys can pull this off, You're amazing!

 

[Aladin Akyurek]

Hi
C3:I8 contain cells that list a number. I've been using 5229 as an example. (several duplicates of numbers will be in this array but only one per cell)
A3:A8 contain peoples names. (no names are duplicated)
If C3:I8 contains 5229 then same row column A would be pasted on different sheet, say Sheet4 B2. If multiple names need to be placed in cell B2 could they appear as a list?

Thanks for the clear problem description.

Sheet1 (data)

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
3​	jack		85​	106​	89​	97​	60​	114​	49​
4​	mark		67​	79​	26​	102​	50​	109​	122​
5​	ian		42​	74​	61​	61​	106​	62​	31​
6​	yan		92​	102​	20​	78​	70​	49​	102​
7​	dave		73​	115​	61​	108​	100​	85​	71​
8​	zack		124​	120​	84​	84​	81​	61​	64​

Sheet2 (processing)

Row\Col	B​
1​	61​
2​	ian
3​	dave
4​	zack
5​
6​

B1 houses a number of interest.

In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$3:$A$8,SMALL(IF(FREQUENCY(IF(1-(Sheet1!$A$3:$A$8=""),IF(Sheet1!$C$3:$I$8=$B$1,MATCH(Sheet1!$A$3:$A$8,Sheet1!$A$3:$A$8,0))),ROW(Sheet1!$A$3:$A$8)-ROW(Sheet1!$A$3)+1),ROW(Sheet1!$A$3:$A$8)-ROW(Sheet1!$A$3)+1),ROWS($B$2:B2))),"")

 

[Graysleaf]

You guys Rock!
So when I past array in Sheet2 B2 only A3 appears from Sheet1.
Is there a way to have these pulled from Sheet1 into a text box on Sheet2?
Greatly appreciated

 

[Graysleaf]

In your example above with Sheet1 (data);

I'd like "current day" (column C) to place all names listed in column A whom have "85" in current day to be pasted into text box on Sheet2.
If there is more than one "85" listed in column C then place all names of same row from column A into list on Sheet2 text box.

 

[Graysleaf]

Please let me know if I'm asking confusing questions, I try to simplify the questions and add things on to clarify the request.
If that's too confusing just let me know and I'll see if I can articulate what the final product is that I'm looking for.