how to merge the 2 work sheets, 1 column records slightly difference

[sivavem]

Please advice and revert.
I currently have two work sheets
:-> work sheet 1 - Store Details and few duplicate redords too
Store NAME, warehouse ID, store address....
:-> work sheet 2 - warehouse id, weekly paid
Please note that 2 worksheet's warehouse id's records are little bit difference
example work sheet 1's warehouse id has 002345 and worksheet 2's warehouse id has 2345 ie 2 extra 00's
Now is there a way to merge worksheet 1 and 2 together into one sheet or file?

Any help will be very much appreciated!

 

[hiker95]

sivavem,

Welcome to the MrExcel forum.

What version of Excel are you using?

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:

 

[excelsishya]

Hi,

See if it works for you

it assumes id in worksheet1 is '00425 type.

workshhet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 78px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Store name</td> <td>id</td> <td>adresss</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>A</td> <td style="text-align: right;">00235</td> <td style="text-align: right;">100</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>B</td> <td style="text-align: right;">00325</td> <td style="text-align: right;">200</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>C</td> <td style="text-align: right;">00125</td> <td style="text-align: right;">300</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>=VLOOKUP((MID(B2,3,LEN(B2)-2))+0,workshhet2!$A$2:$B$4,2,0)</td></tr> <tr> <td>C3</td> <td>=VLOOKUP((MID(B3,3,LEN(B3)-2))+0,workshhet2!$A$2:$B$4,2,0)</td></tr> <tr> <td>C4</td> <td>=VLOOKUP((MID(B4,3,LEN(B4)-2))+0,workshhet2!$A$2:$B$4,2,0)</td></tr></tbody></table></td></tr></tbody></table>

worksheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 67px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>id</td> <td>PAYMENT</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">235</td> <td style="text-align: right;">100</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">325</td> <td style="text-align: right;">200</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">125</td> <td style="text-align: right;">300</td></tr></tbody></table>

 

[excelsishya]

Forgot u told duplicates

in such case create helper column in sheet1

worksheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 78px;"> <col style="width: 64px;"> <col style="width: 69px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Store name</td> <td>id</td> <td>helper col</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>A</td> <td style="text-align: right;">00235</td> <td style="text-align: right;">235</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>B</td> <td style="text-align: right;">00325</td> <td style="text-align: right;">325</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>C</td> <td style="text-align: right;">00125</td> <td style="text-align: right;">125</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>C</td> <td style="text-align: right;">00125</td> <td style="text-align: right;">125</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>=(MID(B2,3,LEN(B2)-2))+0</td></tr> <tr> <td>C3</td> <td>=(MID(B3,3,LEN(B3)-2))+0</td></tr> <tr> <td>C4</td> <td>=(MID(B4,3,LEN(B4)-2))+0</td></tr> <tr> <td>C5</td> <td>=(MID(B5,3,LEN(B5)-2))+0</td></tr></tbody></table></td></tr></tbody></table>

worksheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 67px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>id</td> <td>PAYMENT</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">235</td> <td style="text-align: right;">100</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">325</td> <td style="text-align: right;">200</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">125</td> <td style="text-align: right;">300</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">125</td> <td style="text-align: right;">150</td></tr></tbody></table>


extract unique id using advanced filter and run sumif in summary/combined sheet.

summary

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 213px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>unique</td> <td>unique from Advanced filter</td> <td>Result</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>A</td> <td style="text-align: right;">235</td> <td style="text-align: right;">100</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>B</td> <td style="text-align: right;">325</td> <td style="text-align: right;">200</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>C</td> <td style="text-align: right;">125</td> <td style="text-align: right;">450</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>D5</td> <td>=SUMIF(worksheet2!$A$2:$A$5,summary!C5,worksheet2!$B$2:$B$5)</td></tr> <tr> <td>D6</td> <td>=SUMIF(worksheet2!$A$2:$A$5,summary!C6,worksheet2!$B$2:$B$5)</td></tr> <tr> <td>D7</td> <td>=SUMIF(worksheet2!$A$2:$A$5,summary!C7,worksheet2!$B$2:$B$5)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4