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)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

3​

[/td][td]jack[/td][td][/td][td]

85​

[/td][td]

106​

[/td][td]

89​

[/td][td]

97​

[/td][td]

60​

[/td][td]

114​

[/td][td]

49​

[/td][/tr]
[tr][td]

4​

[/td][td]mark[/td][td][/td][td]

67​

[/td][td]

79​

[/td][td]

26​

[/td][td]

102​

[/td][td]

50​

[/td][td]

109​

[/td][td]

122​

[/td][/tr]
[tr][td]

5​

[/td][td]ian[/td][td][/td][td]

42​

[/td][td]

74​

[/td][td]

61​

[/td][td]

61​

[/td][td]

106​

[/td][td]

62​

[/td][td]

31​

[/td][/tr]
[tr][td]

6​

[/td][td]yan[/td][td][/td][td]

92​

[/td][td]

102​

[/td][td]

20​

[/td][td]

78​

[/td][td]

70​

[/td][td]

49​

[/td][td]

102​

[/td][/tr]
[tr][td]

7​

[/td][td]dave[/td][td][/td][td]

73​

[/td][td]

115​

[/td][td]

61​

[/td][td]

108​

[/td][td]

100​

[/td][td]

85​

[/td][td]

71​

[/td][/tr]
[tr][td]

8​

[/td][td]zack[/td][td][/td][td]

124​

[/td][td]

120​

[/td][td]

84​

[/td][td]

84​

[/td][td]

81​

[/td][td]

61​

[/td][td]

64​

[/td][/tr]
[/table]

Sheet2 (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

61​

[/td][/tr]
[tr][td]

2​

[/td][td]ian[/td][/tr]
[tr][td]

3​

[/td][td]dave[/td][/tr]
[tr][td]

4​

[/td][td]zack[/td][/tr]
[tr][td]

5​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][/tr]
[/table]

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.