Formula to fill in fields when cell is blank

[flagimodiere]

Hi

I have a spreadsheet that I contains duplicate applicants with same acct number. I need to make a table by column to identify how many applicants to a loan.

Sample of dats

Acct. name
123. Joe
Jim


I want to create another column to say if A2 is blank then use field a1. Then in another column I want to say applicant 1 and applicant 2

My output would look like this


Acct. applicant 1. Applicant 2.

Now by date is row format


Acct. applicant 1
Applicant 2



Thanks

 

[Claus Busch]

Hi,

Am Thu, 10 Oct 2013 00:14:24 -0700 (PDT) schrieb (e-mail address removed):

Acct. name
123. Joe
Jim

your data in Sheet1. Then the following macro will write your data to
Sheet2:
Sub Test()
Dim i As Long
Dim j As Long
Dim LRow As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
i = 1
For Each rngC In .Range("A2:A" & LRow)
If Len(rngC) > 0 Then
i = i + 1
j = 3
Sheets("Sheet2").Cells(i, 1) = rngC
Sheets("Sheet2").Cells(i, 2) = rngC.Offset(, 1)
Else
Sheets("Sheet2").Cells(i, j) = rngC.Offset(, 1)
j = j + 1
End If
Next
End With
End Sub


Regards
Claus B.

 

[Hermann Maier]

ty thats is fine for me, too

Am 10.10.2013 09:14, schrieb (e-mail address removed):