Help: Sorting 2 columns according to matching cells, and fishing for duplicates

[Mazin]

I have been working on this macro for about three days. I can't seem to

figure out how to do this (primarily the part where it checks column A,

described below), and I have searched endlessly, but nothing fits what
Im looking for. I cannot download any add-ins, since my work network
security will not allow it.

I will try to explain this the best that I can (sorry for the long
explanation, but I want to be very clear).

I want it to:

- Read B1
- Read next row (B2)
- If next row (B2) = B1, then read next row (B3)
- Continue reading next row until cell does not = B1

- If next row does not = B1, then select the rows in column A that are
adjacent to all rows read in above steps (so if B1, B2, B3 and B4 are
all the same, then A1, A2, A3 and A4 should all be checked in the step
below)
- Check for any duplicates in these selected rows (there should be no
duplicates)
- If there are any duplicate cells in the rows checked in column A,
then all these rows (duplicate and non-duplicate) are to be selected
and copied into a new sheet (copy, not cut), and the original cells in
the original sheet are to be all highlighted yellow. Then proceed to
read the next cell in column B following the last read cell.
- If there are no duplicates, then it should proceed to read the next
cell in column B following the last read cell.
- NOTE: The above three steps should be ignored if no duplicates are
found in column B. In this case it should just move on to the next
cell.
- This should continue reading the next cell in B and looping the
process until it reaches a cell with the text "END" in it, where it
will end there.

Here is a visual example of what the sheet looks like (but it is about
8,000 to 10,000 rows down):

Column A Column B
Grostone 10D1
Grostone 10D3
Grostone 10D3
EXTx 10D3
PAP 10D3
PAP 10D4
PAP 10D9
PAP 10DE1A
PAP 10DE1B
PAP 10DE1C
PAP 10DE1D
END END

So, in this case, "10D1" is read and ignored since there are no
duplicates. "10D3" should be read four times (B2, B3, B4, B5), and so
lines A2, A3, A4 and A5 should be checked for duplicates. The two
"Grostone"s should be detected and so everything in lines 2, 3, 4 and 5

are copied and pasted into a new sheet (called "Duplicates"), and then
these lines (in original sheet) are all highlighted yellow. All other
cells are read in column B, and the macro terminates at the word "END".


I would really appreciate any help you can offer with this. Thanks in
advance.

 

[-Grover]

Mazin,

It seems like you're going to an awful lot of trouble trying to
write a macro for something that you can probably get in a simple pivot
table. Building one off of these two columns and using both fields as
"Row Fields", you'd be able to visualize this a lot easier, and you can
copy and paste right off the pivot table into your "Duplicates" sheet.
It won't flag them yellow...but I'm not sure if that's really necessary
when you have data like this that updates automatically.

HTH

-G

 

[Mazin]

sigh, yeah, I just realized that a simple "=B1=B2" in column C that
increases in range down column B solved the whole thing.

lol...three days of trial and error, and it was as simple as a handful
of characters.