compare multiple columns in excel and insert blank if no match

[ram123]

i would be grateful if you help me in solving this problems

my excel looks like this

A B C D

1 2 1 1

2 1 2 2

3 1 1 2

4 4 4 1


i want this to be arranged in

A B C D

1 - 1 1

2 - 2 2

3 - - -

4 4 4 -


Meaning that if there no match in other columns it should be left blank

Thank you very much

 

[NARAYANK991]

Hi Ram ,


Can you confirm if my understanding is correct ?


1. Does column A have the row numbers 1,2,3,4,5,6,7,... ?


2. Should the numbers match with the row number i.e. in the first row 1 should have duplicates , in the second row 2 should have duplicates and so on ? Does this mean that in the 7th row , if the number 7 does not have duplicates , it should not appear in the output ? In row 3 , the number 1 appears twice , but it does not appear in the output since it does not match with the row number.


Narayan

 

[ram123]

Hi Narayan


May be i have not explained properly, let explain it again

i have a excel sheet with number of columns and containing number of rows may be 10000

i want to check for example if A column contains 1 t 30 and B has numbers frm 1 to 30 with some missing numbers and same with C. which looks like this

1 3 1

2 5 2

3 7 4

4 10 6

5 14 7

6 17 8

7 21 12

8 24 22

9 26 27

10 27 28

11 30 29

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

and the output looks like this

1 - 1

2 - 2

3 3 -

4 - 4

5 5 -

6 - 6

7 7 7

8 - 8

9 - -

10 10 -

11 - -

12 - 12

13 - -

14 14 -

15 - -

16 - -

17 17 -

18 - -

19 - -

20 - -

21 21 -

22 - 22

23 - -

24 24 -

25 - -

26 26 -

27 27 27

28 - 28

29 - 29

30 30 -


i want to know whether there is any method to populate this.

It don't necessarily a dash it can be blanks space or any colour etc., but when comparing i want all relevant same numbers in one row and if not a blank


Hope this will clarify.


thanks

 

[NARAYANK991]

Hi Ram ,


Assuming that your data is in columns A , B and C ( you can extend this to more columns ) , and your output is in columns G , H and I , then column G will replicate the numbers in column A i.e. the numbers in column G will be the same as the numbers in column A.


In column H , starting from H1 , enter the following formula :


=IF(ISNA(MATCH($G1,$B$1:$B$30,0)),"-",$G1)


In column I , starting from I1 , enter the following formula :


=IF(ISNA(MATCH($G1,$C$1:$C$30,0)),"-",$G1)


Copy these down.


Since you say your data extends to 1000 rows , the above addresses will have to be changed , so that instead of covering only 30 rows , you consider all 1000.


Narayan

 

[ram123]

Hi Narayan


Thank you very much its working exactly how I wanted.


Cheers