macro needed

  • Thread starter Thread starter shaltar
  • Start date Start date
S

shaltar

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.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
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.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
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.
Click to expand...
from http://www.ExcelTip.com/forumat 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
+----------------------------------------------------------------
 
Back
Top