Sort data on sheet2 to data on sheet1

mjexcel · Mar 20, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>

Here’s what I’m trying to do:

Concept:
Maintain a history of changes to part numbers. Part number data resides on sheet1 and history resides on sheet2

How I think I will manage the data:
Sort rows of data on sheet2 to data on sheet1 (same order, including blanks).
I have common data in column A of each sheet.
Some cells in column A are blank and I need to maintain their position.
There may be rows of data added or deleted to/from sheet1 from time to time.
If row(s) of data are added to sheet1 I would like to get the data (single cell, column A) added to sheet 2.
If data is deleted from sheet1 I would like to get the corresponding data in sheet2 (entire row) moved to the bottom of the list on sheet2.
<o

> </o

>
Sample data:
<o

>Sheet1</o

>

<table style="border-collapse: collapse; width: 190pt;" border="0" cellpadding="0" cellspacing="0" width="252"><col style="width: 17pt;" width="22"> <col style="width: 65pt;" width="86"> <col style="width: 54pt;" width="72" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 17pt;" width="22" height="20">
</td> <td class="xl65" style="border-left: medium none; width: 65pt;" width="86">A</td> <td class="xl65" style="border-left: medium none; width: 54pt;" width="72">B</td> <td class="xl65" style="border-left: medium none; width: 54pt;" width="72">C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Part Number</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Part Name</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Part Usage</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0212399</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">blank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202254</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202259</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">blank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202260</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
Sheet2
<table style="border-collapse: collapse; width: 219pt;" border="0" cellpadding="0" cellspacing="0" width="291"><col style="width: 14pt;" width="19"> <col style="width: 65pt;" width="86"> <col style="width: 70pt;" width="93" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 14pt;" width="19" height="20">
</td> <td class="xl65" style="border-left: medium none; width: 65pt;" width="86">A</td> <td class="xl65" style="border-left: medium none; width: 70pt;" width="93">B</td> <td class="xl65" style="border-left: medium none; width: 70pt;" width="93">C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Part Number</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Revision Info.</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Revision Info.</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202254</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 2 info</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 1 info</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">blank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">blank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202259</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 2 info</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 1 info</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0202260</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 2 info</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 1 info</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">blank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A0212399</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 2 info</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Rev 1 info</td> </tr> </tbody></table>

Many thanks in advance.

mj
<o

> </o

>

PCRIDE · Mar 20, 2010

The only thing that I can think of is you format your table as a "Table" in excel, this way you can keep the sorting when new data is added.

Not sure how you would keep blank rows where they remain, because if you sort then the blanks would be at the bottom or top depending on your sorting option.

If your not deleting the entire row and only the values the on sheet 2 just match up the rows corrispoding to each sheet using this formalua to combine all the data into one cell.

You can put some logic into the forumula for the blank rows

=IF(Sheet1!A1="","",Sheet1!A1&" "&Sheet1!B1&" "&Sheet1!C1)

Is this what you are looking for?

mjexcel · Mar 21, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CUser%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>

Thank you PCRIDE.
<o

> </o

>
I don’t think that is what I’m looking for.
<o

> </o

>
Sheet1 is my master list of active parts. I may add or remove parts over time.
Sheet2 is my revision history record (no deleting of data, cells or rows).
If I remove a part from Sheet1 (master) I want to keep the historical data of that part on Sheet2 (revision history) but I want to move it to the bottom of the Sheet2 list.
<o

> </o

>
I want to keep the part number on the two Sheets in the same absolute order. That may not be a correct use of the term absolute in this context. But if part number A123456 resides on Sheet1, row20, I want the Sheet2 data for A123456 to reside on Sheet2, row 20 as well.
<o

> </o

>
<o

> </o

>
mj

PCRIDE · Mar 21, 2010

I see... yea you would need VB to do this, I am not sure though.

Sort data on sheet2 to data on sheet1

mjexcel

New Member

PCRIDE

Well-known Member

mjexcel

New Member

PCRIDE

Well-known Member

Similar threads

Share this page

Sort data on sheet2 to data on sheet1

mjexcel

New Member

PCRIDE

Well-known Member

mjexcel

New Member

PCRIDE

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock