Sorting a range with respect to other range

[somnath6309]

Sorting a range with respect to other range


B

C

1

DAVE

JOHN W.K

2

SARA J

DANIAL K

3

BILL J

PETER G

4

SOMNATH

ANIL

5

SOURAV

PULAK

6

ASIM

DAVE L

7

JOHN WK

SARA

8

DANIAL.K

ANKIT

9

PETER

BILL JELEN

10

WALKER

MIKE JACKSON

11

MIKE JACK

WALKER JOHN

12

SAYAN

JAMES BOND

13

JAMES BN

SAEKHER


Most of the persons in Col B are same as persons in Col C. Actually the names may differ e.g. Dave and Dave L, Peter G and Peter etc.

We have to sort the range C1 to C13 with respect to the elements in B1 to B13. Following are the requirements:

I. Compare each element of range C1:C13 with each element of Range B1:B13 with the help of vba LIKE Operator.

II. If one element of col C approximately matches with one element of Col B, then the matching elements will be arranged side by side and hence, the range C1:C13 will be sorted with respect to range B1:B13

III. There are elements in both the columns that does not match each other e.g. SOMNATH, SOURAV, ASIM etc. In Col B & ANIL, PULAK, ANKIT etc. In Col C. These elements will be kept at last of both the columns.

The finished product created manually and the same is as follows:


B

C

1

DAVE

DAVE L

2

SARA J

SARA

3

BILL J

BILL JELEN

4

JOHN WK

JOHN W.K

5

DANIAL.K

DANIAL K

6

PETER

PETER G

7

WALKER

WALKER JOHN

8

MIKE JACK

MIKE JACKSON

9

JAMES BN

JAMES BOND

10

SAYAN

SAEKHER

11

SOMNATH

ANIL

12

SOURAV

PULAK

13

ASIM

ANKIT


Now, is it possible to create Sub / & Function procedure in vba that can accomplish the task ?

 

[NARAYANK991]

Hi ,

You can wait for answers or you can upload a sample workbook.

Narayan