I had a reuqest to create a macro for excel to search for and merge
cells that contain the same text into one cell with a value in the next
column of how many cells were merged. I don't do a lot of macro
programming, so this was a little beyond me.

Can anyone help? Thanks in advance.

Neil.



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

Just to clarify, attached is the spreadsheet that I was given to work
with. It's a sell-thru report and it shows each item seperately, we
need the repeats merged and a column added to show how many of each
item that there was.



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

Oops...

File Attached: http://www.exceltip.com/forum/attach...?postid=221250 (nikonjuly.xls)

------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

Shalter,

Suggested is that you try the following experimentally.
It will require extensive testing to ensure it doesn't
corrupt data under some circumstances. Experiment on a
copy of the data. You cannot undo a macro unlike what
you're used to.

You didn't say that you needed to delete the rows made
empty by this process in order to compress the data so I
left it as is. A suggested alternative is to not merge
the cells but to:
1) Count the number of same Item numbers in column A
2) Record the result to to column C
3) Delete the rows containing the duplicates

From viewing your data, it was apparent that all repeating
values are contiguous. The code therefore assumes that
this will always be the case.

Sub ConsolidateData()
Dim i As Integer, ii As Integer, iii As Integer
Dim NumRows As Integer
NumRows = Range("A65536").End(xlUp).Row
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Columns("A:B").VerticalAlignment = xlVAlignTop
For i = 3 To NumRows + 3
For ii = i + 1 To NumRows + 3
If Trim(Cells(ii, 1)) = Trim(Cells(i, 1)) Then
iii = iii + 1
Else
If iii 0 Then
Range(Cells(i, 1), Cells(ii - 1, 1)).MergeCells = True
Range(Cells(i, 2), Cells(ii - 1, 2)).MergeCells = True
End If
Cells(i, 3) = iii + 1
i = ii - 1
iii = 0
Exit For
End If
Next ii
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

To repeat the above, experiment rigorously before using.

Regards,
Greg


-----Original Message-----
I had a reuqest to create a macro for excel to search for
and merge
cells that contain the same text into one cell with a
value in the next
column of how many cells were merged. I don't do a lot of
macro
programming, so this was a little beyond me.

Can anyone help? Thanks in advance.

Neil.



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly
from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions
at http://www.ExcelTip.com/
------------------------------------------------
.

Thanks, that seems to work very well... no glitches so far. :-)



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

Alas, this is not working out the way I thought it was. The file that
orginally was working with had only two columns, Item and Description
The macro is counting the number of same items in column a and mergin
ok, but the new spreadsheet I have to work with has a third column wit
quantity as well.

So running the macro on this is throwing the quantities totally out o
whack.

It's difficult to explain, attached is an example of what I am takin
about.

I hope someone can help out!

Thanks

+----------------------------------------------------------------
| Attachment filename: wtnov.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=363516
+----------------------------------------------------------------

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com