Excel1: Complex Macro.

[aacod]

Under column A from cells 2 to 85000 I have multiple duplicate entries: e.g. INT, INT, INT.
In the corresponding cells under column B for all the INT, it means Internal, International, Interval.

I need a macro that would identify all duplicate entries in Column A, copy the contents from corresponding cells in column B and paste to one of the cells corresponding to ‘INT’ in column B separated by ‘Or’. e.g. as follows:


Sheet1

*	A	B	C	D	E
1	*	*	*	*	*
2	*	*	*	Befor Macro	*
3	*	*	*	*	*
4	INT	Internal	*	*	*
5	INT	International	*	*	*
6	INT	Interval	*	*	*
7	*	*	*	*	*
8	*	*	*	After Macro	*
9	*	*	*	*	*
10	INT	Internal or International or Interval	*	*	*
11	*	*	*	*	*
12	*	*	*	*	*
13	*	*	*	*	*
14	*	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[aacod]

Peter,

Sorry for the delay.

I will test the macro this weekend and inform you.

Appreciate it.

Thanks.

aacod

 

[Peter_SSs]

Peter,

Sorry for the delay.

I will test the macro this weekend and inform you.

Appreciate it.

Thanks.

aacod

No problem. I was wondering what happened to you.

 

[aacod]

Peter,

The macro does what I requested. GREAT, PERFECT.

Can you modify to delete duplicate entries from Column E & F?

Thanks.

aacod.

 

[Peter_SSs]

Can you modify to delete duplicate entries from Column E & F?

Not immediately obvious to me what that means. Could you give a small set of dummy data & explain in relation to that?

 

[aacod]

Peter,

Sorry not from Column E.

Delete duplicate entries from Column F.

e.g.

Column E would have: INT

Column F would have: Internal, International, Internal, Integer, International, International.

Result in column F should be: Internal, Integer, International.

HTH

aacod.

 

[Peter_SSs]

Peter,

Sorry not from Column E.

Delete duplicate entries from Column F.

e.g.

Column E would have: INT

Column F would have: Internal, International, Internal, Integer, International, International.

Result in column F should be: Internal, Integer, International.

HTH

aacod.

I believe that my macro from post #10 already eliminates duplicates (as did my macros from posts #4 & #6). Can you provide a small set of sample data where you think this is not the case?

 

[aacod]

Before:

Sheet1

Sheet1

*	A	B
1	Int	Internal or International or Integer or
2	Int	internal or Interest or Intermittent or Intestinal
3	int	intent or Interfill or Intensity or Intermittent
4	int	internal or international or International
5	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4




After #6 and #10

Sheet1

*	A	B	C	D	E	F
1	Int	Internal or International or Integer or	*	*	*	*
2	Int	internal or Interest or Intermittent or Intestinal	*	*	int	Internal Or Interest Or Intermittent Or Intestinal or Intent Or Interfill Or Intensity Or Intermittent or Internal Or International Or International
3	int	intent or Interfill or Intensity or Intermittent	*	*	*	*
4	int	internal or international or International	*	*	*	*
5	*	*	*	*	*	*
6	*	*	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Ii does not recognize the lower and upper case character of the word in column B, e.g Internal, internal, International and international, and so it does not delete the duplicates in column F.

HTH

aacod

 

[Peter_SSs]

It seems to me that the issue has nothing to do with recognising upper/lower case, but that you have dramatically changed the requirement which was:

Under column A from cells 2 to 85000 I have multiple duplicate entries: e.g. INT, INT, INT.
In the corresponding cells under column B for all the INT, it means Internal, International, Interval.

I need a macro that would identify all duplicate entries in Column A, copy the contents from corresponding cells in column B and paste to one of the cells corresponding to ‘INT’ in column B separated by ‘Or’.

That is exactly what the macro has done, apart from using "Proper Case" which you asked for in post #7 and using column F for the results. If you look at your data in cells B2, B3 and B4, none of those cells are the same as each other (that is, no duplicates) so their values were copied to column F, joined by "Or" and the whole lot put into proper case.

There was never any examples or description of data in column B that needed to be split into components, which is what you now seem to be expecting.

 

[aacod]

Peter_SSs,

Sorry for misrepresenting or not explaining correctly, but is it possible what I want to achieve? Delete all duplicates from Column 'B'.

Thanks

aacod

 

[Peter_SSs]

Can you make perfectly clear what "splitting" needs to be done for column B entries?

For example, we previously had the multi-word entries "with regards to" and "that is" that apparently do not require splitting.
Is the only rule to split if the text string " or " appears in the cell?
If not please specify clearly what the 'rules' are.