How can I format this spread sheet

Hi, rdefino.

Obviously there's lots of information on B that isn't shown on A. Is that because you haven't shown us all of A or you simply don't have the information?

(1) If there's more information, then please take one of your existing entries which has full information and "Harry Potterise" it.

(2) Which fields are compulsory for your import programs? If you don't know then take your entry which has the minimum no. of completed fields, manually enter into the "Harry Potter" layout and try to load it. (Make sure to check where there is no firstname/lastname, e.g. "Dad". Should "Dad" go into firstname or lastname?)

(3) How are names such as "Harry Potter" recorded in your existing data - "Harry Potter", "Potter, Harry" or something else?

Thanks,
Brian.

1. The main problem is that you have 6 phone number columns, but you need to end up with only 3 (Mobile, WorkPhone and HomePhone).  Pick the 3 columns that you don't want (it seems to me that E, F & H are the most empty and should be disposed of).  Scroll down your whole document looking at columns E, F & H.  If you see a phone number, cut and paste it onto one of the other columns (C, D or G).
2. Now you'll delete some columns.  Left-click on column H (up on the grey 'H') so that the whole H column is highlighted.  Right-click column H and go to 'Delete'.  Do the same to columns F, E and B, in that order.
3. No you'll insert a column.  Right-click column A and go to 'Insert'.  Do it again, so that 2 empty columns have appeared.
4. Now label some empty columns.  Go to cell A1 and type 'Groupname'.  Go to cell B1 and type 'Lastname'.  Go to cell G1 and type 'Company'.  Go to cell H1 and type 'Emailaddress'.  Go to cell I1 and type 'Image'.
5. Now change the label of some other columns.  Go to cell D1 (which currently says 'General') and change it to 'HomePhone'.  Go to cell F1 (which currently says 'Work') and change it to 'WorkPhone'.
6. Now shift one column.  Right-click column C (which is labelled 'HomePhone') and go to 'Cut'.  Now right-click on column G (which should say 'Company') and go to 'Insert Cut Cells'.
Done.
Obviously some of your columns have no data (Lastname, Company, Emailaddress and Image) because none was available in your source data.

App reads mapping "instructions" on mapping tab to determine how to map from Capture A to Capture B.  Don't add any data to the right of rows 1/2 unless its to guide this mapping from A to B.

On the main tab, clear Capture A test data, then paste your Capture A (old data) into the tab.  Use the macro button to clear Capture B and map data to the Capture B tab based on the rules in the Mapping tab.

Here's the code:

Option Explicit
Sub clearCaptureA()
Dim wkb As Workbook
Dim wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Capture A")
    
    wks.Range("A2", wks.Cells(wks.Rows.Count, 1)).EntireRow.ClearContents
    
End Sub
Sub clearCaptureB()
Dim wkb As Workbook
Dim wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Capture B")
    
    wks.Range("A2", wks.Cells(wks.Rows.Count, 1)).EntireRow.ClearContents
    
End Sub
Sub transferData()
Dim wkb As Workbook
Dim wksA As Worksheet
Dim wksB As Worksheet
Dim wksMap As Worksheet
Dim rng As Range
Dim r As Range
Dim outCursor As Range
Dim dictCaptureA As Dictionary

    Set wkb = ThisWorkbook
    Set wksA = wkb.Sheets("Capture A")
    Set wksB = wkb.Sheets("Capture B")
    Set wksMap = wkb.Sheets("Mapping")
    
    Set dictCaptureA = CreateObject("Scripting.Dictionary")
    
    'clear wksB
    wksB.Range("A2:A" & wksB.Rows.Count).EntireRow.ClearContents
       
    'read in rules for mapping to Capture B from Capture A
    Set rng = wksMap.Range("A2", wksMap.Cells(2, wksB.Columns.Count).End(xlToLeft))
    For Each r In rng
        If r.Value <> vbNullString Then
            dictCaptureA.Add Key:=r.Value, Item:=r.Column 'Capture A data goes into identified column for Capture B
        End If
    Next r
    
    'now read Capture A and map to Capture B, using these rules
    Set rng = wksA.Range("A2", wksA.Range("A" & wksA.Rows.Count).End(xlUp))
    Set rng = rng.Resize(, wksA.Cells(1, wksA.Columns.Count).End(xlToLeft).Column)
    Set outCursor = wksB.Range("A2")
    
    For Each r In rng
        Debug.Print wksA.Cells(1, r.Column).Value, dictCaptureA.Exists(wksA.Cells(1, r.Column).Value)
        If dictCaptureA.Exists(wksA.Cells(1, r.Column).Value) Then 'we know where to map this one
            wksB.Cells(r.Row, dictCaptureA(wksA.Cells(1, r.Column).Value)).Value = r.Value
        End If
    Next r
    
    dictCaptureA.RemoveAll
    Set dictCaptureA = Nothing
    
End Sub

Select allOpen in new window

See attached.
portTOiPhone-r1.xls

Very nice, Dave! Solid material for an article?

Trivial suggestion...
The OP's input phone numbers are left adjusted. To avoid losing data, perhaps make "Capture B" cells Text?

Excellent point - and more than trivial - I think you're right.  Just in case that's needed, here it is updated, to ensure that dashes in phone numbers, long phone numbers, etc., are captured:

Option Explicit
Sub clearCaptureA()
Dim wkb As Workbook
Dim wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Capture A")
    
    wks.Range("A2", wks.Cells(wks.Rows.Count, 1)).EntireRow.ClearContents
    
End Sub
Sub clearCaptureB()
Dim wkb As Workbook
Dim wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Capture B")
    
    wks.Range("A2", wks.Cells(wks.Rows.Count, 1)).EntireRow.ClearContents
    
End Sub
Sub transferData()
Dim wkb As Workbook
Dim wksA As Worksheet
Dim wksB As Worksheet
Dim wksMap As Worksheet
Dim rng As Range
Dim r As Range
Dim outCursor As Range
Dim dictCaptureA As Dictionary

    Set wkb = ThisWorkbook
    Set wksA = wkb.Sheets("Capture A")
    Set wksB = wkb.Sheets("Capture B")
    Set wksMap = wkb.Sheets("Mapping")
    
    Set dictCaptureA = CreateObject("Scripting.Dictionary")
    
    'clear wksB
    wksB.Range("A2:A" & wksB.Rows.Count).EntireRow.ClearContents
       
    'read in rules for mapping to Capture B from Capture A
    Set rng = wksMap.Range("A2", wksMap.Cells(2, wksB.Columns.Count).End(xlToLeft))
    For Each r In rng
        If r.Value <> vbNullString Then
            dictCaptureA.Add Key:=r.Value, Item:=r.Column 'Capture A data goes into identified column for Capture B
        End If
    Next r
    
    'now read Capture A and map to Capture B, using these rules
    Set rng = wksA.Range("A2", wksA.Range("A" & wksA.Rows.Count).End(xlUp))
    Set rng = rng.Resize(, wksA.Cells(1, wksA.Columns.Count).End(xlToLeft).Column)
    Set outCursor = wksB.Range("A2")
    
    For Each r In rng
        Debug.Print wksA.Cells(1, r.Column).Value, dictCaptureA.Exists(wksA.Cells(1, r.Column).Value)
        If dictCaptureA.Exists(wksA.Cells(1, r.Column).Value) Then 'we know where to map this one
            wksB.Cells(r.Row, dictCaptureA(wksA.Cells(1, r.Column).Value)).Value = "'" & r.Text
        End If
    Next r
    
    dictCaptureA.RemoveAll
    Set dictCaptureA = Nothing
    
End Sub

Select allOpen in new window

See attached.

Dave
portTOiPhone-r2.xls

Hi dlmille,

first off, thank you all the work you did!  But I'm not an excel person at all, so I think I need more guidance on this.

Capture A has more data than what's needed to be moved over to capture B. Capture is an example of how the program needs to have the spread sheet formatted.   So any thing that's not needed for capture B and be lost when moving data from Capture A.

But like I said I'm not an excel person so I getting lost on the process you wrote.

"On the main tab, clear Capture A test data, then paste your Capture A (old data) into the tab.  Use the macro button to clear Capture B and map data to the Capture B tab based on the rules in the Mapping tab."


any chance you could write for a six year old. LOL

Not really but if you could detail it a little easier for me to follow.

Thanks!!!

First, go to the mapping tab.  Row 1 is how the data is organized for Capture B.  Row 2 is how the data is organized and mapped TO capture B from capture A.

Take a look at that, first.  Does the mapping make sense?  Are there any more fields that should come from Capture A to Capture B?

When you're done with that, let me know.

Dave