Aligning columns based on a matching "key field" value

pranavc · Jul 1, 2011

Hello All,
I am new to this forum. I am a business school student and am currently in the midst of my summer internships. Given the quantitative nature of my tasks, it goes without saying that I will be relying excessively on Excel.

I have a problem I have been struggling with for the last few hours.
I have two tables in excel and they are both in the same worksheet. Table 1 looks like this:
A B C
<table class="MsoTableGrid" style="border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="width: 119.7pt; border: 1pt solid windowtext; padding: 0in 5.4pt;" valign="top" width="160"> Asset ID
</td> <td style="width: 119.7pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160"> Quantity sold
</td> <td style="width: 119.7pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160"> Category
</td> </tr> <tr style=""> <td style="width: 119.7pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="160"> 12345
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160">
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160"> Type 1
</td> </tr> <tr style=""> <td style="width: 119.7pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="160"> 240001
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160">
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160"> Type 2
</td> </tr> <tr style=""> <td style="width: 119.7pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="160"> 34003
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160">
</td> <td style="width: 119.7pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="160"> Type 3
</td> </tr> </tbody></table>
Table 2 looks like this:
H I J
<table class="MsoTableGrid" style="border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="width: 159.6pt; border: 1pt solid windowtext; padding: 0in 5.4pt;" valign="top" width="213"> Asset ID
</td> <td style="width: 159.6pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213"> Characteristic 1 [Number]
</td> <td style="width: 159.6pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213"> Characteristic 2 [Number]
</td> </tr> <tr style=""> <td style="width: 159.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="213"> 240001
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> </tr> <tr style=""> <td style="width: 159.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="213"> 34003
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> </tr> <tr style=""> <td style="width: 159.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="213"> 12345
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> <td style="width: 159.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="213">
</td> </tr> </tbody></table>
I need to merge the two tables such that the entries from Table 1 align with Table 2 based on matching values of the “Asset ID” field. There is another catch. In Table 1, there can be two entries for the same asset ID. The only difference is the value of “category” associated with each duplicate entry of the “asset ID”. In this case, I want both “category” values to be captured in the same cell (but on separate lines).

I was wondering if any of you could provide some guidance on how I might go about doing this. I realize this is a loaded question but any input would be of great help to me.

Thank you in advance!

Pranav

nightcrawler23 · Jul 2, 2011

I hope u were lookin for something like this

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

1

Asset ID

Quantity sold

Category

Asset ID

Characteristic 1 [Number]

Characteristic 2 [Number]

Characteristic 3 [Number]

2

12345

Type 1

240001

Type 2

Type 4

Type 2

3

240001

Type 2

34003

Type 3

Type 6

#N/A

4

34003

Type 3

12345

Type 1

Type 8

#N/A

5

240001

Type 4

6

12345

Type 8

7

240001

Type 2

Asset ID

Characteristic 1 [Number]

Characteristic 2 [Number]

Characteristic 3 [Number]

8

34003

Type 6

240001

1

3

5

7

9

240001

Type 4

34003

1

4

8

#N/A

10

12345

1

2

6

#N/A

Sheet1

Excel 2007

Cell Formulas
Range	Formula
I2	=OFFSET($A$1,I8-1,2)
I3	=OFFSET($A$1,I9-1,2)
I4	=OFFSET($A$1,I10-1,2)
I8	=H8+MATCH($G8,INDIRECT("A"&(H8+1)&":A9"),0)
I9	=H9+MATCH($G9,INDIRECT("A"&(H9+1)&":A9"),0)
I10	=H10+MATCH($G10,INDIRECT("A"&(H10+1)&":A9"),0)
J2	=OFFSET($A$1,J8-1,2)
J3	=OFFSET($A$1,J9-1,2)
J4	=OFFSET($A$1,J10-1,2)
J8	=I8+MATCH($G8,INDIRECT("A"&(I8+1)&":A9"),0)
J9	=I9+MATCH($G9,INDIRECT("A"&(I9+1)&":A9"),0)
J10	=I10+MATCH($G10,INDIRECT("A"&(I10+1)&":A9"),0)
K2	=OFFSET($A$1,K8-1,2)
K3	=OFFSET($A$1,K9-1,2)
K4	=OFFSET($A$1,K10-1,2)
K8	=J8+MATCH($G8,INDIRECT("A"&(J8+1)&":A9"),0)
K9	=J9+MATCH($G9,INDIRECT("A"&(J9+1)&":A9"),0)
K10	=J10+MATCH($G10,INDIRECT("A"&(J10+1)&":A9"),0)

I have an extra table just like your 2nd table for ease of calculation.

pranavc · Jul 3, 2011

First, a huge "thank you" for taking the time to respond. I actually want a single row to only contain information for a single "Asset ID".

For instance, for asset ID "12345", I only want its information on that row - not information pertaining to other asset IDs. I hope I have explained that somewhat well. Here is what I would like my final table to look like:

 <table class="MsoNormalTable" style="" width="513" border="1" cellpadding="0" cellspacing="0"> <thead> <tr style="height: 14.55pt;"> <td style="width: 51.5pt; padding: 0in; height: 14.55pt;" width="69">

[FONT=&quot]H[/FONT]

</td> <td colspan="2" style="width: 77.25pt; padding: 0in; height: 14.55pt;" valign="top" width="103">

[FONT=&quot] [/FONT]

</td> <td style="width: 61pt; padding: 0in; height: 14.55pt;" width="81">

[FONT=&quot]I[/FONT]

</td> <td style="width: 13.5pt; padding: 0in; height: 14.55pt;" valign="top" width="18">

[FONT=&quot] [/FONT]

</td> <td style="width: 72.4pt; padding: 0in; height: 14.55pt;" width="97">

[FONT=&quot]J[/FONT]

</td> <td style="width: 109.2pt; padding: 0in; height: 14.55pt;" width="146">

[FONT=&quot]K[/FONT]

</td> </tr> </thead> <tbody><tr style="height: 98.75pt;"> <td style="width: 51.5pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" width="69"> [FONT=&quot]Asset ID[/FONT]
</td> <td colspan="2" style="width: 77.25pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" valign="top" width="103"> [FONT=&quot]Category[/FONT]
</td> <td style="width: 61pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" width="81"> [FONT=&quot]Characteristic 1 [Number][/FONT]
</td> <td style="width: 13.5pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" width="97"> [FONT=&quot]Characteristic 2 [Number][/FONT]
</td> <td style="width: 109.2pt; border: 1pt solid black; padding: 0in; height: 98.75pt;" width="146"> [FONT=&quot]Characteristic 3 [Number][/FONT]
</td> </tr> <tr style="height: 28.3pt;"> <td style="width: 51.5pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="69">

[FONT=&quot]12345[/FONT]

</td> <td colspan="2" style="width: 77.25pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" valign="top" width="103"> [FONT=&quot]Type 1[/FONT]
[FONT=&quot]Type 8[/FONT]
</td> <td style="width: 61pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="81"> [FONT=&quot]1[/FONT]
</td> <td style="width: 13.5pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="97"> [FONT=&quot]2[/FONT]
</td> <td style="width: 109.2pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="146"> [FONT=&quot]3[/FONT]
</td> </tr> <tr style="height: 28.3pt;"> <td style="width: 51.5pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="69">

[FONT=&quot]240001[/FONT]

</td> <td colspan="2" style="width: 77.25pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" valign="top" width="103"> [FONT=&quot]Type2[/FONT]
[FONT=&quot]Type 4[/FONT]
</td> <td style="width: 61pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="81"> [FONT=&quot]1[/FONT]
</td> <td style="width: 13.5pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="97"> [FONT=&quot]2[/FONT]
</td> <td style="width: 109.2pt; border: 1pt solid black; padding: 0in; height: 28.3pt;" width="146">

[FONT=&quot]3[/FONT]

</td> </tr> <tr style="height: 13.8pt;"> <td style="width: 51.5pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" width="69">

[FONT=&quot]34003[/FONT]

</td> <td style="width: 75.25pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" valign="top" width="100"> [FONT=&quot]Type 3[/FONT]
</td> <td colspan="2" style="width: 63pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" width="84"> [FONT=&quot]1[/FONT]
</td> <td style="width: 13.5pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" width="97"> [FONT=&quot]2[/FONT]
</td> <td style="width: 109.2pt; border: 1pt solid black; padding: 0in; height: 13.8pt;" width="146">

[FONT=&quot]3[/FONT]

</td> </tr> <tr style="height: 14.55pt;"> <td style="width: 51.5pt; padding: 0in; height: 14.55pt;" width="69">
</td> <td style="width: 75.25pt; padding: 0in; height: 14.55pt;" valign="top" width="100"> [FONT=&quot] [/FONT]
</td> <td colspan="2" style="width: 63pt; padding: 0in; height: 14.55pt;" width="84">
</td> <td style="width: 13.5pt; padding: 0in; height: 14.55pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; padding: 0in; height: 14.55pt;" width="97">
</td> <td style="width: 109.2pt; padding: 0in; height: 14.55pt;" width="146">
</td> </tr> <tr style="height: 13.8pt;"> <td style="width: 51.5pt; padding: 0in; height: 13.8pt;" width="69">
</td> <td style="width: 75.25pt; padding: 0in; height: 13.8pt;" valign="top" width="100"> [FONT=&quot] [/FONT]
</td> <td colspan="2" style="width: 63pt; padding: 0in; height: 13.8pt;" width="84">
</td> <td style="width: 13.5pt; padding: 0in; height: 13.8pt;" valign="top" width="18"> [FONT=&quot] [/FONT]
</td> <td style="width: 72.4pt; padding: 0in; height: 13.8pt;" width="97">
</td> <td style="width: 109.2pt; padding: 0in; height: 13.8pt;" width="146">
</td> </tr> </tbody></table>
Once again, thank you for responding!

nightcrawler23 · Jul 5, 2011

I dont understand. What is this Characteristic 1 [Number] and where is this data coming from. Do you just want the formula to get the values in category column only???

pranavc · Jul 5, 2011

nightcrawler23 said:
I dont understand. What is this Characteristic 1 [Number] and where is this data coming from. Do you just want the formula to get the values in category column only???

Hi nightcrawler,
The Characteristic 1 [Number] is just a field. It isn't really of intrinsic value to the "table merge" that I am attempting. It is basically just two tables I am trying to merge such that there is only one unique Asset ID per row (no duplicate Asset IDs in the table). I hope I am making some sense here. Thank you once again for taking the time to help me with this.

Regards,

Pranav

pranavc · Jul 5, 2011

I figured trying to achieve this with a macro is the way to go. This is the macro I have so far:

Sub DoubleCycle()

i = 2

Do Until Worksheets("Duplicated Asset IDs").Cells(i, 1) = ""

j = 2

While Worksheets("Unique Asset ID table").Cells(i, 1) <> ""

If Worksheets("Duplicated Asset IDs").Cells(i, 1) = Worksheets("Unique Asset ID table").Cells(j, 1) Then

Worksheets("Duplicated Asset IDs").Cells(i, 6) = Worksheets("Unique Asset ID table").Cells(j, 2)
Worksheets("Duplicated Asset IDs").Cells(i, 7) = Worksheets("Unique Asset ID table").Cells(j, 3)
Worksheets("Duplicated Asset IDs").Cells(i, 8) = Worksheets("Unique Asset ID table").Cells(j, 4)
Worksheets("Duplicated Asset IDs").Cells(i, 9) = Worksheets("Unique Asset ID table").Cells(j, 5)
Worksheets("Duplicated Asset IDs").Cells(i, 10) = Worksheets("Unique Asset ID table").Cells(j, 6)
Worksheets("Duplicated Asset IDs").Cells(i, 11) = Worksheets("Unique Asset ID table").Cells(j, 7)
Worksheets("Duplicated Asset IDs").Cells(i, 12) = Worksheets("Unique Asset ID table").Cells(j, 8)
Worksheets("Duplicated Asset IDs").Cells(i, 13) = Worksheets("Unique Asset ID table").Cells(j, 9)

j = 13000

Else

j = j + 1

End If

Wend

i = i + 1

Loop

End Sub

But for some odd reason, I get an "application-defined or object-defined error". I am unable to figure out what is going on here. If anyone else has any insights into what the mistake could be, it would be great if you could let me know. Thank you in advance!

Regards,

Pranav

pranavc · Jul 5, 2011

I posted my macro issue on here too soon. I figured out my error!

Aligning columns based on a matching "key field" value

pranavc

New Member

Excel Facts

nightcrawler23

Well-known Member

pranavc

New Member

nightcrawler23

Well-known Member

pranavc

New Member

pranavc

New Member

pranavc

New Member

Similar threads

Forum statistics

Share this page

Aligning columns based on a matching "key field" value

pranavc

New Member

Excel Facts

nightcrawler23

Well-known Member

pranavc

New Member

nightcrawler23

Well-known Member

pranavc

New Member

pranavc

New Member

pranavc

New Member

Similar threads

Forum statistics

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