REALLY REALLY Need Your Help: Macro for duplicates and multiple values

MsChuchi

New Member
Joined
Jun 25, 2013
Messages
1
Hello Everyone,
I really really need your help on my macro problems.
I am sooo bad in macro and have very limited knowledge, so all help that you can give will really be appreciated.
Here's my problem:
I need to create macro for the folloing:
1. Find the duplicates in sheet 1 to sheet 2 to one specific column, pull out the other data and placed it on another sheet.
2. For the duplicates that start with PR with Blank values, copy the data of the other sheet.
2. Find the non-duplicate in sheet 1 and place it again with its data into another sheet.
3. Find the non-duplicate in sheet 2 and place it again with its data into another sheet.
4. Please make the color fonts the same with what i have below.

Hope I explained everything well..
To illustrate, here's what I need:

Sheet 1:
A
B
C
D
E
F
G
H
I
J
001
1
PR TEST1
001
D
D1
D2
D3
002
2
HC TEST2
100
C1
C2
C3
C4
C5
C6
003
3
HC TEST3
C
A
B
C
D
E
F
004
4
HCTEST4
001
D1
D2
D3
100
5
PR TEST5
A003
E1
E2
E3
E4
#5
E6
200
6
PR TEST6
1234
B
C
D
E
001
D
C001
7
HC TEST7
1234
A
C
D
B
D
300
9
TEST9
002
AB
S
D
F
400
10
TEST10
110
D
G
I
A004
8
PR TEST8
C
A
B
C
D
E
F

<TBODY>
</TBODY>

Expected Results
Sheet2: Unique Duplicate Values from sheet 1-- Baseline Column to find the duplicate is Column D (Column #4)

A
B
C
D
E
F
G
H
I
J
001
1
PR TEST1
001
D
D1
D2
D3
004
3
HCTEST4
001
D1
D2
D3
003
3
HC TEST3
C
A
B
C
D
E
F
A004
8
PR TEST 8
C
A
B
C
D
E
F
200
6
PR TEST6
1234
B
C
D
E
001
D
C001
7
HC TEST7
1234
A
C
D
B
D

<TBODY>
</TBODY>

Sheet 3: Unique Duplicate values from Sheet 2 -- copy use PR blank data for HC or vice versa data for columns E-J.
A
B
C
D
E
F
G
H
I
J
001
1
PR TEST1
001
D
D1
D2
D3
D3
004
3
HCTEST4
001
D
D1
D2
D3
D3
003
3
HC TEST3
C
A
B
C
D
E
F
A004
8
PR TEST 8
C
A
B
C
D
E
F
200
6
PR TEST6
1234
B
C
D
E
001
D
C001
7
HC TEST7
1234
A
C
D
E
B
D

<TBODY>
</TBODY>

Sheet 4: Non - Unique Duplicate values from Sheet 1 that starts in "PR" in column C --Baseline Column to find the duplicate is Column D (Column #4)

A
B
C
D
E
F
G
H
I
J
100
5
PR TEST5
A003
E1
E2
E3
E4
#5
E6

<TBODY>
</TBODY>


Sheet 5: Non - Unique Duplicate values from Sheet 1 that starts in "HC" in column C --Baseline Column to find the duplicate is Column D (Column #4)

A
B
C
D
E
F
G
H
I
J
002
2
HC TEST2
100
C1
C2
C3
C4
C5
C6

<TBODY>
</TBODY>



Sheet 6: Non - Unique Duplicate values from Sheet 1 that DOESN'T starts in EITHER "HC" OR "PR" in column C --Baseline Column to find the duplicate is Column D (Column #4)


A
B
C
D
E
F
G
H
I
J
300
9
TEST9
002
AB
S
D
F
400
10
TEST10
110
D
G
I

<TBODY>
</TBODY>
Sheet 7: Pull out all records that has a prefix of "PR" from sheet 3 column C and change prefix "PR" to "ABM".

A
B
C
D
E
F
G
H
I
J
001
1
ABM TEST1
001
D
D1
D2
D3
D3
A004
8
ABM TEST 8
C
A
B
C
D
E
F
200
6
ABM TEST6
1234
B
C
D
E
001
D

<TBODY>
</TBODY>

There you go!!! This kind of macro is very complex for me and obviously I'm having a hard time creating a macro that would suit my needs. Pleaseeeeeeeeeee Help!!!!

Btw, I will be doing this to aprox 50,000 records (50k, yes, seriously..can you just imagine if I'll do this kind of validation manually :() so I hope the macro won't run that long.

Thanks much!!
Really appreciate it.

Chuchi. :)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top