[Help] To eliminate almost identical datas to be 1 & merge the differences among them

[Gravity101]

Hey guys, really need help from you with my excel work, and looks like i stuck in this point.

So i have a sheet, contains many datas.
The key of every row is No.PEB.
so the situation is like here :
i have a row the No.PEB is ex: 037175, this row has like more than 20 columns.

the problem is i have 3 rows of No.PEB with number 037175 the only difference is the last column of every row is different,

and i want to eliminate them, and merge the difference to be only one row.


Ugh, i don;t think i explain it well, so the exmple is here:


No.PEB------------Date-----------------Documents----------------CodeOfDocs

037175-------------7/7/11---------------AWB-------------------------12345

037175-------------7/7/11---------------BL-----------------------------85858

037175-------------7/7/11---------------Invoice-----------------------54765


nah, i want to make it to be


No.PEB------------Date-----------------AWB-----------------BL-------------------Invoice

037175-------------7/7/11---------------12345-------------85858------------------54765



thanks in advance guys.

 

[hiker95]

Gravity101,

Welcome to the MrExcel forum.

What version of Excel are you using?


We can not tell what rows/columns your data is in.


You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[markmzz]

Try this:

Use Advanced Filter (with the option Only unique records) to create the data of Columns F and G of my example.

Then use the formula below (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">NoPEB</td><td style=";">Date</td><td style=";">Documnts</td><td style=";">CodeOfDoc</td><td style="text-align: right;;"></td><td style=";">NoPEB</td><td style=";">Date</td><td style=";">AWB</td><td style=";">BL</td><td style=";">Invoice</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">037175</td><td style="text-align: right;;">7/7/2011</td><td style=";">AWB</td><td style="text-align: right;;">12345</td><td style="text-align: right;;"></td><td style="text-align: right;;">037175</td><td style="text-align: right;;">7/7/2011</td><td style="text-align: right;;">12345</td><td style="text-align: right;;">85858</td><td style="text-align: right;;">54765</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">037175</td><td style="text-align: right;;">7/7/2011</td><td style=";">BL</td><td style="text-align: right;;">85858</td><td style="text-align: right;;"></td><td style="text-align: right;;">037176</td><td style="text-align: right;;">7/7/2011</td><td style="text-align: right;;">23672</td><td style="text-align: right;;">59371</td><td style="text-align: right;;">95070</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">037175</td><td style="text-align: right;;">7/7/2011</td><td style=";">Invoice</td><td style="text-align: right;;">54765</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">037176</td><td style="text-align: right;;">7/7/2011</td><td style=";">AWB</td><td style="text-align: right;;">23672</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">037176</td><td style="text-align: right;;">7/7/2011</td><td style=";">BL</td><td style="text-align: right;;">59371</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">037176</td><td style="text-align: right;;">7/7/2011</td><td style=";">Invoice</td><td style="text-align: right;;">95070</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">(<font color="Green">$A$2:$A$7=$F2</font>)*(<font color="Green">$C$2:$C$7=H$1</font>),$D$2:$D$7,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Gravity101]

hi hiker95, thanks for the reply

i use Ms. Excel 2007.

unfortunately, box.net doesn't allow me to upload files, because my Firefox is outdated, do u mind if i upload it to mediafire?

here's the sample
http://www.mediafire.com/?xnsncc9utbswdbh

thanks hiker95

 

[Gravity101]

oh hi markmzz, thanks, def gon try it.

thanks dude.

 

[markmzz]

Gravity101,

Here is your sheet and the formula (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>W</th><th>X</th><th>Y</th><th>Z</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #C0C0C0;;">NO_PEB</td><td style="background-color: #C0C0C0;;">CARRIER</td><td style="background-color: #C0C0C0;;">AWB</td><td style="background-color: #C0C0C0;;">INVOICE</td><td style="background-color: #C0C0C0;;">PACKING LIST</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">037175</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ277</td><td style=";">PMS-042-2010</td><td style=";">PMS-042-2010</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">037870</td><td style=";">SINGAPORE AIR</td><td style=";">SRG7ABZ304</td><td style=";">PMS-044-2010</td><td style=";">PMS-044-2010</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">039835</td><td style=";">SINGAPORE AIRLINE</td><td style=";">SRG7ABZ355</td><td style=";">PMS-048-2010</td><td style=";">PMS-048-2010</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">042436</td><td style=";">SINGAPORE AIR</td><td style=";">SRG7ABZ417</td><td style=";">PMS-053-2010</td><td style=";">PMS-053-2010</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">042496</td><td style=";">SINGAPORE AIR</td><td style=";">SRG7ABZ446</td><td style=";">PMS-054-2010</td><td style=";">PMS-054-2010</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">043917</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ529</td><td style=";">PMS-058-2010</td><td style=";">PMS-058-2010</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">043917</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ529</td><td style=";">PMS-058-2010</td><td style=";">PMS-058-2010</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">043917</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ529</td><td style=";">PMS-058-2010</td><td style=";">PMS-058-2010</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">043917</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ529</td><td style=";">PMS-058-2010</td><td style=";">PMS-058-2010</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">044106</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7ABZ527</td><td style=";">PMS-057-2010</td><td style=";">PMS-057-2010</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">058264</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7AVN063</td><td style=";">PMS-052-2011</td><td style=";">PMS-052-2011</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">062947</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG 7AVN109</td><td style=";">PMS-055-2011</td><td style=";">PMS-055-2011</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">062947</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG 7AVN109</td><td style=";">PMS-055-2011</td><td style=";">PMS-055-2011</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">064060</td><td style=";">QANTAS AIRLINES</td><td style=";">SRG-0016766</td><td style=";">PMS-081-2010</td><td style=";">PMS-081-2010</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">064384</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7AVN187</td><td style=";">PMS-056-2011</td><td style=";">PMS-056-2011</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">064385</td><td style=";">QANTAS AIRWAYS</td><td style=";">SRG7AVN192</td><td style=";">PMS-057-2011</td><td style=";">PMS-057-2011</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">064413</td><td style=";">ROYAL BRUNEI</td><td style=";">SRG-0016764</td><td style=";">PMS-080-2010</td><td style=";">PMS-080-2010</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">064413</td><td style=";">ROYAL BRUNEI</td><td style=";">SRG-0016764</td><td style=";">PMS-080-2010</td><td style=";">PMS-080-2010</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">064413</td><td style=";">ROYAL BRUNEI</td><td style=";">SRG-0016764</td><td style=";">PMS-080-2010</td><td style=";">PMS-080-2010</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">X2</th><td style="text-align:left">{=INDEX(<font color="Blue">'Sheet 1'!$Y$2:$Y$1621,MAX(<font color="Red">(<font color="Green">'Sheet 1'!$A$2:$A$1621=$A2</font>)*(<font color="Green">'Sheet 1'!$X$2:$X$1621=X$1</font>)*(<font color="Green">ROW(<font color="Purple">$Y$2:$Y$1621</font>)-ROW(<font color="Purple">$Y$1</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Gravity101]

hi markmzz, i try it, unfortunately i just got 0
and i already entere it with Ctrl+Shift+Enter and the curly braces do appear.

please, kindly tell me where did i do wrong?

 

[markmzz]

Another formula (no array formula):

=INDEX('Sheet 1'!$Y$2:$Y$1621,SUMPRODUCT(--('Sheet 1'!$A$2:$A$1621=$A2),--('Sheet 1'!$X$2:$X$1621=X$1),(ROW($Y$2:$Y$1621)-ROW($Y$1))))

Markmzz

 

[markmzz]

Gravity101,

Look at your PM.

Markmzz

 

[markmzz]

Gravity101,

Look at your email.

Markmzz
<!-- / message -->