Consolidating data with a formula.

[Mitchofocala]

I need to identify (and eliminate) duplicate cells in column "A" and combine/merge correlating cells (cells adjacent to duplicate cells) in column "B" I need to combine so that I don't lose information in the cells in column "B". I was hopeful that I could create a formula that could achieve this. If you need more detail or an example I can provide. I would appreciate any assistance I'm currently consolidating data by manually copying and pasting. Also I am using Excel 2007.

 

[Superstar31]

Can I see the data? Or just a small snip it of it.

 

[Mitchofocala]

300001 12/20/2013 (63); 11/1/2013 (62)300024 11/26/2013 (62)300025 1/22/2014 (63)300030 10/3/2013 (63)300030 9/27/2013 (62)300031 9/19/2013 (63)300039 10/2/2013 (63)300051 11/1/2013 (63)300055 11/18/2013 (63)300072 9/18/2013 (63)300102 9/20/2013 (63)300102 9/20/2013 (62)300110 9/23/2013 (63)300110 9/23/2013 (62)300121 9/18/2013 (62)300129 12/23/2013 (63)300129 12/23/2013 (62)300130 9/22/2013 (62)300159 1/12/2014 (63)300159 1/12/2014 (62)300219 10/4/2013 (63)300219 10/4/2013 (62)300221 10/22/2013 (62)300226 9/27/2013 (63)300226 9/24/2013 (62)300227 1/16/2014 (62)300236 10/1/2013 (63)300236 9/30/2013 (62)300244 10/29/2013 (63)300244 10/29/2013 (62)300249 9/30/2013 (62)300257 10/15/2013 (63)300257 10/7/2013 (62)300262 1/18/2014 (63)300262 1/18/2014 (62)300267 11/18/2013 (63)300267 11/18/2013 (62)300272 12/31/2013 (63)300272 12/28/2013 (62)

 

[Superstar31]

Can you attach a file so it looks cleaner? I can't really tell which is the end or start

 

[Superstar31]

I might have made it over complicated but here is my shot at it...

Create 4 additional rows in front of A

So now A,B,C,D,E should be blank
In Column A type this " =COUNTIF($E$1:E1,E1) "
In Column B type this " =CONCATENATE(E1,A1) "
In Column C type this " =SUMIF($E:$E,E1,A:A) "
In Column D type this " =SUM(A1+C1) "

E & F is your data

In Column G type this " =IF(A1=2,CONCATENATE(E1," ",F1,"; ",VLOOKUP(E1,$E:$F,2,FALSE)),CONCATENATE(E1," ",F1)) "
In Column H type this " =IF(D1=2,G1,IF(D1=5,G1,"")) "

So now I'll attempt to explain what i was trying to accomplish in each column
Column A is counting the times a number shows up in column E (Hopefully a max of 2)
Column B is creating an individual identify for each number
Column C is counting how many times a number is duplicated (based on the 1 & 2 count)
Column G is combining the number you have in column E with whatever is in column F, BUT combining the duplicates like you requested
Column H is cleaning it up

 

[Superstar31]

Excel 2010
	A	B	C	D	E	F	G	H
1	1	3000011	1	2	300001	12/20/2013 (63); 11/1/2013 (62)	300001 12/20/2013 (63); 11/1/2013 (62)	300001 12/20/2013 (63); 11/1/2013 (62)
2	1	3000241	1	2	300024	11/26/2013 (62)	300024 11/26/2013 (62)	300024 11/26/2013 (62)
3	1	3000251	1	2	300025	1/22/2014 (63)	300025 1/22/2014 (63)	300025 1/22/2014 (63)
4	1	3000301	3	4	300030	10/3/2013 (63)	300030 10/3/2013 (63)
5	2	3000302	3	5	300030	9/27/2013 (62)	300030 9/27/2013 (62); 10/3/2013 (63)	300030 9/27/2013 (62); 10/3/2013 (63)
6	1	3000311	1	2	300031	9/19/2013 (63)	300031 9/19/2013 (63)	300031 9/19/2013 (63)
7	1	3000391	1	2	300039	10/2/2013 (63)	300039 10/2/2013 (63)	300039 10/2/2013 (63)
8	1	3000511	1	2	300051	11/1/2013 (63)	300051 11/1/2013 (63)	300051 11/1/2013 (63)
9	1	3000551	1	2	300055	11/18/2013 (63)	300055 11/18/2013 (63)	300055 11/18/2013 (63)
10	1	3000721	1	2	300072	9/18/2013 (63)	300072 9/18/2013 (63)	300072 9/18/2013 (63)
11	1	3001021	3	4	300102	9/20/2013 (63)	300102 9/20/2013 (63)
12	2	3001022	3	5	300102	9/20/2013 (62)	300102 9/20/2013 (62); 9/20/2013 (63)	300102 9/20/2013 (62); 9/20/2013 (63)
13	1	3001101	3	4	300110	9/23/2013 (63)	300110 9/23/2013 (63)
14	2	3001102	3	5	300110	9/23/2013 (62)	300110 9/23/2013 (62); 9/23/2013 (63)	300110 9/23/2013 (62); 9/23/2013 (63)
15	1	3001211	1	2	300121	9/18/2013 (62)	300121 9/18/2013 (62)	300121 9/18/2013 (62)
16	1	3001291	3	4	300129	12/23/2013 (63)	300129 12/23/2013 (63)
17	2	3001292	3	5	300129	12/23/2013 (62)	300129 12/23/2013 (62); 12/23/2013 (63)	300129 12/23/2013 (62); 12/23/2013 (63)
18	1	3001301	1	2	300130	9/22/2013 (62)	300130 9/22/2013 (62)	300130 9/22/2013 (62)
Sheet2