I have two columns, for simpicity I'll name them column A and Column B.

To illustrate my data on sheet1:

Column A header column B header
Record 1 <blank
<blank 05/20/06 Record 1 related info-A
<blank 06/29/06 Record 1 related info-B
Record 2 <blank
<blank 06/29/06 Record 2 related info-A
<blank 03/01/07 Record 2 related info-B
<blank 04/05/07 Record 2 related info-C
<blank 06/22/08 Record 2 related info-D
Record 3 <blank
<blank 01/29/08 Record 3 related info-A
<blank 07/29/08 Record 3 related info-B
<blank 02/27/09 Record 3 related info-C

Note, <blank means empty cell

So basically I need a macro to get the latest entry info from column B
and moved to the <blank cell adjacent to column A for each item
existing in column A
and delete all other blank rows in column A.

So after the macro, it should look like:

Column A column B
Record 1 06/29/06 Record 1 related info-B
Record 2 06/22/08 Record 2 related info-D
Record 3 02/27/09 Record 3 related info-C


Note, all items in column A and column B are dynamic and cannot specify
a fixed range.


Any help on hard coding to get me started would be appreciated.


Thank you.


*** Sent via Developersdex http://www.developersdex.com ***

Polo78,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim i As Integer

With Columns("B:B").SpecialCells(xlCellTypeBlanks)
For i = 1 To .Areas.Count - 1
.Areas(i)(1).Formula = "=" & .Areas(i + 1)(0).Address
Next i
..Areas(.Areas.Count)(1).Formula = _
"=" & IIf(.Areas(.Areas.Count)(3) = "", _
.Areas(.Areas.Count)(2).Address, _
.Areas(.Areas.Count)(2).End(xlDown).Address)
End With
Columns("B:B").Copy
Columns("B:B").PasteSpecial xlPasteValues
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub


"Polo78 Lacoste" wrote in message
...
I have two columns, for simpicity I'll name them column A and Column B.

To illustrate my data on sheet1:

Column A header column B header
Record 1 <blank
<blank 05/20/06 Record 1 related info-A
<blank 06/29/06 Record 1 related info-B
Record 2 <blank
<blank 06/29/06 Record 2 related info-A
<blank 03/01/07 Record 2 related info-B
<blank 04/05/07 Record 2 related info-C
<blank 06/22/08 Record 2 related info-D
Record 3 <blank
<blank 01/29/08 Record 3 related info-A
<blank 07/29/08 Record 3 related info-B
<blank 02/27/09 Record 3 related info-C

Note, <blank means empty cell

So basically I need a macro to get the latest entry info from column B
and moved to the <blank cell adjacent to column A for each item
existing in column A
and delete all other blank rows in column A.

So after the macro, it should look like:

Column A column B
Record 1 06/29/06 Record 1 related info-B
Record 2 06/22/08 Record 2 related info-D
Record 3 02/27/09 Record 3 related info-C


Note, all items in column A and column B are dynamic and cannot specify
a fixed range.


Any help on hard coding to get me started would be appreciated.


Thank you.


*** Sent via Developersdex http://www.developersdex.com ***

Thank you so much!! Coding was flawleses.

Thank you in advance.

Newbie to VBA.

*** Sent via Developersdex http://www.developersdex.com ***

I have another dilemma, hope you help me out on this one. Original data
has 4 columns, driven by column A. This is somewhat a transpose but on
multiple columns.

Before macro run

Column A column B Column C Column D Column E
Client 1 item qty date1 date2
<blank item2 qty date1 date2
<blank item3 qty date1 date2
Client 2 item qty date1 date2
<blank item2 qty date1 date2
Client 3 item qty date1 date2
<blank item2 qty date1 date2
<blank item3 qty date1 date2
<blank item4 qty date1 date2


After macro run

Column A Column F
Client 1 item-qty-date; item2-qty-date2; item3-qty-date2
Client 2 item-qty-date; item2-qty-date2;
Client 3 item-qty-date; item2-qty-date2; item3-qty-date2;
item4-qty-date2


After the macro is finished, any blank data in column A should be
deleted. Also Column F should be appended to Column A-E (totalling 6
columns when report is done).

Need VBA macro coding to help out and get started.

Thank you in advance.

Newbie to VBA.

*** Sent via Developersdex http://www.developersdex.com ***

Hi

Try this:

Sub bbb()
FirstRow = 2 'Headings in row 1
LastRow = Range("B" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
If Cells(r, 1) = "" Then
LastCol = Range("A" & TargetRow).End(xlToRight).Column
Cells(r, 2).Resize(1, 4).Cut Destination:=Cells(TargetRow,
LastCol + 1)
Else
TargetRow = r
End If
Next
Range("A1", Cells(TargetRow, 1)).SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
End Sub

Regards,
Per

On 7 Maj, 03:05, Polo78 Lacoste wrote:
I have another dilemma, hope you help me out on this one. Original data
has 4 columns, driven by column A. This is somewhat a transpose but on
multiple columns.

Before macro run

Column A * *column B Column C Column D *Column E
Client 1 * *item * * qty * * *date1 * * date2
<blank * * item2 * *qty * * *date1 * * * date2
<blank * * item3 * *qty * * *date1 * * * date2
Client 2 * *item * * qty * * *date1 * * date2
<blank * * item2 * *qty * * *date1 * * * date2 *
Client 3 * *item * * qty * * *date1 * * date2 *
<blank * * item2 * *qty * * *date1 * * * date2
<blank * * item3 * *qty * * *date1 * * * date2
<blank * * item4 * *qty * * *date1 * * * date2

After macro run

Column A * Column F
Client 1 * item-qty-date; item2-qty-date2; item3-qty-date2
Client 2 * item-qty-date; item2-qty-date2; *
Client 3 * item-qty-date; item2-qty-date2; item3-qty-date2;
item4-qty-date2

After the macro is finished, any blank data in column A should be
deleted. Also Column F should be appended to Column A-E (totalling 6
columns when report is done).

Need VBA macro coding to help out and get started.

Thank you in advance.

Newbie to VBA.

*** Sent via Developersdexhttp://www.developersdex.com***

I forgot to mention that all the columns on F and after, should all be
in one cell, so when there is a vlookup on a column A, column F has all
the data. Im trying to figure out how to concat the cells data. You did
an excellent job by the way.

Thank you in advance.

Newbie to VBA.

*** Sent via Developersdex http://www.developersdex.com ***