Hi,
. I am not sure if you want a formula or a VBA program.? But I am a beginner practicing with VBA sorting Programs. I wrote a program for you to be going on with. See how you get on with it and if it is in the direction of what you want. It is very simple, inflexible and assumes for example your data headings are always in the same order. Once we know exactly wot you want, that is to say how your data in the practice could look like (Heading order, maximum number of data Rows (records) , etc. we can do something better!
. I put the macro in the first sheet (“BUSINESS VISA”) Module of the file I return to you Here:
FileSnack | Easy file sharing
. If you do want a VBA program but are not sure how to run it, then get back and we will explain further. The Code name is care123SimplistSortOFSort.
Here is the code :
<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">' Not necerssary but good idea to help find mistakes- forces you to define variable types</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> care123SimplistSortOFSort()<br><SPAN style="color:#00007F">Dim</SPAN> wkstdata <SPAN style="color:#00007F">As</SPAN> Worksheet, wkstFinal <SPAN style="color:#00007F">As</SPAN> Worksheet, Rngdta <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'give Methods and Properties of the Objects</SPAN><br><SPAN style="color:#00007F">Set</SPAN> wkstFinal = ThisWorkbook.Worksheets("BUSINESS VISA") <SPAN style="color:#007F00">' Set the specific Worksheet</SPAN><br>Set wkstdata = Workbooks("datasheet").Sheets("maincontact") <SPAN style="color:#007F00">' Set the specific Worksheet</SPAN><br>Set Rngdta = wkstdata.UsedRange <SPAN style="color:#007F00">' Set the specific Range (UsedRange Property finds wher it is</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> dtRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, dtCm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, BVRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, BVCm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' limit rows and columns for now to 255</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> dtstRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, dtstCm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, dtEdRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, dtEdCm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, BVedRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'limit rows and columns for now to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> dtstRw = Rngdta.Cells(1, 1).Row + 1<br><SPAN style="color:#00007F">Let</SPAN> BVedRw = wkstFinal.Cells(Rows.Count, 1).End(xlUp).Row<br><SPAN style="color:#00007F">Let</SPAN> dtstCm = Rngdta.Cells(1, 1).Column<br><SPAN style="color:#00007F">Let</SPAN> dtEdRw = wkstdata.Cells(Rows.Count, dtstCm).End(xlUp).Row<br> <SPAN style="color:#00007F">For</SPAN> BVRw = 2 <SPAN style="color:#00007F">To</SPAN> BVedRw <SPAN style="color:#007F00">' take each row in final Table</SPAN><br> <SPAN style="color:#00007F">For</SPAN> dtRw = dtstRw <SPAN style="color:#00007F">To</SPAN> dtEdRw <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' then go through every row in data</SPAN><br> <SPAN style="color:#00007F">If</SPAN> wkstFinal.Cells(BVRw, 1).Value = wkstdata.Cells(dtRw, dtstCm).Value And wkstFinal.Cells(BVRw, 2).Value = wkstdata.Cells(dtRw, dtstCm + 1).Value <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' Check for name match, then if found..</SPAN><br> wkstFinal.Cells(BVRw, 3).Value = wkstdata.Cells(dtRw, dtstCm + 2).Value <SPAN style="color:#007F00">'write phone number in</SPAN><br> wkstdata.Cells(dtRw, dtstCm + 3).Copy Destination:=wkstFinal.Cells(BVRw, 4) <SPAN style="color:#007F00">'Copy Email (Hyperlink) in</SPAN><br> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'otherwise</SPAN><br> <SPAN style="color:#007F00">'Do nothing</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> dtRw <SPAN style="color:#007F00">' look at next data Row</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> BVRw <SPAN style="color:#007F00">' Move on to next final table Roe and go through the data rows again</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'care123SimplistSortOFSort()</SPAN></FONT>
It turns this:
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K |
|---|
| 1 | SURNAME | NAME | Phone | Email | NATIONALITY | SERVICE USED | COMPANY NAME | START VISA | END OF VISA | NEAR EXPIRE | REMARK |
|---|
| 2 | Alan | White | | | | | | | 11.04.15 | N | |
|---|
| 3 | Ali | White | | | | | | | 30.06.14 | X | |
|---|
|
|---|
Into this:
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K |
|---|
| 1 | SURNAME | NAME | Phone | Email | NATIONALITY | SERVICE USED | COMPANY NAME | START VISA | END OF VISA | NEAR EXPIRE | REMARK |
|---|
| 2 | Alan | White | 222 111 33 | w@hotmail.com | | | | | 11.04.15 | N | |
|---|
| 3 | Ali | White | 12-2067851 | aw@gmail.com | | | | | 30.06.14 | X | |
|---|
|
|---|
Alan.
Note:
. Your data file must be open and named as the one you sent. (that could be changed to be any file you have opened)
. Your data can go anywhere in the data sheet, but must have the headings in the order you gave –( that is one of the improvements that could be made, for example to allow for your headings coming in any order). In fact I am learning that you can just about do anything with VBA!! (Once you know how!!).
. Your names must be spelt correctly
. For now the program is limited to 255 Rows.
Alan.
