Conditional Find > Replace Macro

jackieros · Nov 24, 2017

I have an excel (generated from a polling app) in this format:

Person 1 Person 2 Person 3 Person 4
Option A Yes Yes
Option B Yes
Option C Yes

What I ultimately want is a list of the options and who selected each one.

People
Option A Person 1 | Person 4
Option B Person 2
Option C Person 3

====================
What I figured I need to do is first do a conditional find and replace that will check if there a particular column is not blank (or can check if contains "Yes"), and if so, replace that value with same column's row 1 value.

After that, would need to create a new column, where each cell checked in current row, and for each non blank value in that row, copy that value to the current cell.

I assume this will require a macro, which is beyond my Excel abilities. Welcome anyone who can help with this.

wideboydixon · Nov 24, 2017

You could possibly use a formula; depends on how many rows and columns you're working on. For example:

Book1

A

B

C

D

E

1

Person 1

Person 2

Person 3

Person 4

2

Option A

Yes

3

Option B

Yes

4

Option C

Yes

5

6

Option A

Person 1 | Person 4

7

Option B

Person 2

8

Option C

Person 3

Sheet1

Cell Formulas
Range	Formula
B6	{=MID(CONCAT(IF(INDEX($B$2:$E$4,MATCH($A6,$A$2:$A$4,0),)="Yes"," \| " & $B$1:$E$1,"")),4,65535)}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

jackieros · Nov 24, 2017

Thanks for the quick reply, and it's a nice option, but we're talking about around 40 people and 20 options, so I think that would be a bit much to address with a formula...

Gerald Higgins · Nov 24, 2017

=ROW(INDIRECT(MID("WBD",TRUE,NOT(FALSE))&SQRT(SQRT(16))+4))*COLUMN(INDIRECT(MID("OMG",ROUNDUP(LOG(1000,10),0),1)&MATCH(" WBD ",{" WE "," LOVE "," WBD "},0)))

wideboydixon · Nov 24, 2017

jackieros said:
Thanks for the quick reply, and it's a nice option, but we're talking about around 40 people and 20 options, so I think that would be a bit much to address with a formula...

I think it's worth a try!

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

1

Person 1

Person 2

Person 3

Person 4

Person 5

Person 6

Person 7

Person 8

Person 9

Person 10

Person 11

Person 12

Person 13

Person 14

Person 15

Person 16

Person 17

Person 18

Person 19

Person 20

Person 21

Person 22

Person 23

Person 24

Person 25

Person 26

Person 27

Person 28

Person 29

Person 30

Person 31

Person 32

Person 33

Person 34

Person 35

Person 36

Person 37

Person 38

Person 39

Person 40

2

Option A

Yes

3

Option B

Yes

4

Option C

Yes

5

Option D

Yes

6

Option E

Yes

7

Option F

Yes

8

Option G

Yes

9

Option H

Yes

10

Option I

Yes

11

Option J

Yes

12

Option K

Yes

13

Option L

Yes

14

Option M

Yes

15

Option N

Yes

16

Option O

Yes

17

Option P

Yes

18

Option Q

Yes

19

Option R

Yes

20

Option S

Yes

21

Option T

Yes

22

23

24

Option A

25

Option B

26

Option C

27

Option D

28

Option E

29

Option F

30

Option G

31

Option H

32

Option I

33

Option J

34

Option K

35

Option L

36

Option M

37

Option N

38

Option O

39

Option P

40

Option Q

41

Option R

42

Option S

43

Option T

Sheet1

Cell Formulas
Range	Formula
B24	{=MID(CONCAT(IF(INDEX($B$2:$AO$21,MATCH($A24,$A$2:$A$21,0),)="Yes"," \| "&$B$1:$AO$1,"")),4,65535)}
Press CTRL+SHIFT+ENTER to enter array formulas.

:D

wideboydixon · Nov 24, 2017

It's a good job I can stretch my browser window over 2 x 1080p monitors :D

WBD

jackieros · Nov 24, 2017

LOL! Is that why you call yourself "wide boy"

?

And thanks for the suggestion - I'll give it a try!

Conditional Find > Replace Macro

jackieros

New Member

Excel Facts

wideboydixon

Well-known Member

jackieros

New Member

Gerald Higgins

Well-known Member

wideboydixon

Well-known Member

wideboydixon

Well-known Member

jackieros

New Member

Similar threads

Forum statistics

Share this page

Conditional Find > Replace Macro

jackieros

New Member

Excel Facts

wideboydixon

Well-known Member

jackieros

New Member

Gerald Higgins

Well-known Member

wideboydixon

Well-known Member

wideboydixon

Well-known Member

jackieros

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock