How to write a formula to search .....

J

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
199
Hi
I am looking for a formula that allow me to put the vendor number
(blue color in Search Result) & than list out the Items below the
Vendor column in Search Result. (See below)


<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial Narrow,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 104px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-weight: bold; color: rgb(255, 0, 0);">RAW DATA</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold; text-align: center;">Items</td> <td style="font-weight: bold; text-align: center;">Vendor1</td> <td style="font-weight: bold; text-align: center;">Vendor2</td> <td style="font-weight: bold; text-align: center;">Vendor3</td> <td style="font-weight: bold; text-align: center;">Vendor4</td> <td style="font-weight: bold; text-align: center;">Vendor5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold; text-align: center;">A1001</td> <td style="text-align: center;">2</td> <td style="text-align: center;">1</td> <td style="text-align: center;">3</td> <td style="text-align: center;">5</td> <td style="text-align: center;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold; text-align: center;">A1002</td> <td style="text-align: center;">3</td> <td style="text-align: center;">1</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold; text-align: center;">A1003</td> <td style="text-align: center;">3</td> <td style="text-align: center;">5</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="font-weight: bold; text-align: center;">B1001</td> <td style="text-align: center;">4</td> <td style="text-align: center;">5</td> <td style="text-align: center;">2</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="font-weight: bold; text-align: center;">B1002</td> <td style="text-align: center;">1</td> <td style="text-align: center;">3</td> <td style="text-align: center;">4</td> <td style="text-align: center;">5</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold; text-align: center;">B1003</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold; text-align: center;">C1010</td> <td style="text-align: center;">5</td> <td style="text-align: center;">4</td> <td style="text-align: center;">3</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold; text-align: center;">C1011</td> <td style="text-align: center;">4</td> <td style="text-align: center;">2</td> <td style="text-align: center;">1</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold; text-align: center;">C1012</td> <td style="text-align: center;">2</td> <td style="text-align: center;">3</td> <td style="text-align: center;">4</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold;"> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold;"> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="font-weight: bold; color: rgb(255, 0, 0); text-align: left;">SEARCH RESULT</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold; text-align: center;">Vendor</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: center;">1</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: center;">2</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: center;">3</td> <td style="font-weight: bold; color: rgb(0, 0, 255);"> </td> <td style="font-weight: bold; color: rgb(0, 0, 255);"> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td style="font-weight: bold; text-align: center;" rowspan="9">Items</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1001</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1001</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1001</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1002</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1002</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1002</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td style="color: rgb(255, 0, 0); text-align: center;">B1002</td> <td style="color: rgb(255, 0, 0); text-align: center;">B1001</td> <td style="color: rgb(255, 0, 0); text-align: center;">A1003</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td> <td style="color: rgb(255, 0, 0); text-align: center;">C1011</td> <td style="color: rgb(255, 0, 0); text-align: center;">B1003</td> <td style="color: rgb(255, 0, 0); text-align: center;">B1002</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0); text-align: center;">C1011</td> <td style="color: rgb(255, 0, 0); text-align: center;">C1010</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0); text-align: center;">C1012</td> <td style="color: rgb(255, 0, 0); text-align: center;">C1012</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">27</td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">28</td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">29</td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td style="color: rgb(255, 0, 0);"> </td> <td> </td> <td> </td></tr></tbody></table>
Appreciate some can help me
Cheers
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Aladin Akyurek said:
Care to explain why just those items under 1?
Click to expand...

Hi

Not sure I understand your question .... the Search Result is an example what i want. I need a formula in D21:D30, E21:E30 & F21:F30 to extract the items (from the Raw Data) when I input the vendor (1,2,3,4 & 5) in row D20 across. (blue color)
I supposed the function vlookup may work but I do not know how to use it.
Cheers
 
Upvote 0
jamshoot said:
Hi

Not sure I understand your question .... the Search Result is an example what i want. I need a formula in D21:D30, E21:E30 & F21:F30 to extract the items (from the Raw Data) when I input the vendor (1,2,3,4 & 5) in row D20 across. (blue color)
I supposed the function vlookup may work but I do not know how to use it.
Cheers
Click to expand...

Your example confuses...

Why those items under 1 in blue? That is, how that list is derived from the data given above the results area?
 
Upvote 0
Aladin Akyurek said:
Your example confuses...

Why those items under 1 in blue? That is, how that list is derived from the data given above the results area?
Click to expand...

Hi Aladin
The 1 in blue is just for me to seperate the entry & the result below the 1.
Basically, I just want to extract items from the data list from the Raw Data when I enter the Vendor No. The raw data contain list of Vendor 1 to Vendor 5 - supplying items. Some vendors is also supplying same item, some not.
I am trying to create Search Result so that whoever enquire any vendor, I can tell them which are the items they are supplying.
I am familiar with Vlookup function - but I do not know how to extract data which is on the left side of the raw data. I google & noticed that I need to use offset or match combination... but I just cannot get any example to get what I want.
Cheer
 
Upvote 0
jamshoot said:
Hi Aladin
The 1 in blue is just for me to seperate the entry & the result below the 1.
Basically, I just want to extract items from the data list from the Raw Data when I enter the Vendor No. The raw data contain list of Vendor 1 to Vendor 5 - supplying items. Some vendors is also supplying same item, some not.
I am trying to create Search Result so that whoever enquire any vendor, I can tell them which are the items they are supplying.
I am familiar with Vlookup function - but I do not know how to extract data which is on the left side of the raw data. I google & noticed that I need to use offset or match combination... but I just cannot get any example to get what I want.
Cheer
Click to expand...

I have the impression that my question is unclear to you.

Here is abnother effort:

You posted:

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=188><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4408" width=124><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.2pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 25.2pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=34 width=124>SEARCH RESULT</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=124>Vendor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 130.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=172 rowSpan=9 width=124>Items</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>A1001</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19 width=64>A1002</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19 width=64>B1002</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19 width=64>C1011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20> </TD></TR></TBODY></TABLE>

How do we determine that these are the items vendor 1 supplies? No need to discuss a formula... Just give a procedure in words how this list must be produced. I don't see any relation between this list and this data:

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=188><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4408" width=124><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.2pt" height=34><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 25.2pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=34 width=124>RAW DATA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64> </TD></TR><TR style="HEIGHT: 25.2pt" height=34><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 25.2pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=34 width=124>Items</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Vendor1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>A1001</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>A1002</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>A1003</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>B1001</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>B1002</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>B1003</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>C1010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>C1011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=124>C1012</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>2</TD></TR></TBODY></TABLE>

If they are unrelated, try to post an adequate result list which is related to the data.
 
Upvote 0
Hi

Perhaps I should re-arrange the Raw Data with colour in order to explain what I what ..

<table style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="FONT-WEIGHT: bold; WIDTH: 30px"> <col style="WIDTH: 68px"> <col style="WIDTH: 52px"> <col style="WIDTH: 52px"> <col style="WIDTH: 52px"> <col style="WIDTH: 52px"> <col style="WIDTH: 52px"></colgroup> <tbody> <tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"> <td> </td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000">RAW DATA </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td> <td style="FONT-SIZE: 8pt">Items </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center" colspan="5">Vendor Nos</td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">A1001 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">1</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">A1002 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">1</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</td> <td style="FONT-SIZE: 8pt">A1003 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</td> <td style="FONT-SIZE: 8pt">B1001 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">B1002 </td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">1</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</td> <td style="FONT-SIZE: 8pt">B1003 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</td> <td style="FONT-SIZE: 8pt">C1010 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">C1011 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">1</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</td> <td style="FONT-SIZE: 8pt">C1012 </td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000">SEARCH RESULT </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 24px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">Vendor Nos</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">1</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; TEXT-ALIGN: center">2</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; TEXT-ALIGN: center">3</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt"> </td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</td> <td style="FONT-SIZE: 8pt; TEXT-ALIGN: center" rowspan="6">Items </td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">A1001 </td> <td style="FONT-SIZE: 8pt">A1001 </td> <td style="FONT-SIZE: 8pt">A1001 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">A1002 </td> <td style="FONT-SIZE: 8pt">A1002 </td> <td style="FONT-SIZE: 8pt">A1002 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">B1002 </td> <td style="FONT-SIZE: 8pt">B1001 </td> <td style="FONT-SIZE: 8pt">A1003 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</td> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #ffff00">C1011 </td> <td style="FONT-SIZE: 8pt">B1003 </td> <td style="FONT-SIZE: 8pt">B1002 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt">C1011 </td> <td style="FONT-SIZE: 8pt">C1010 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt">C1012 </td> <td style="FONT-SIZE: 8pt">C1012 </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr></tbody></table>
Ok ... What I want is when I key in the vendor nos in row D20 -
a formula in D21 & so on ... is able to use the reference in D20 to extract the items from the raw data.
Hope this is clear to u ...
cheers
 
Upvote 0
jamshoot said:
Hi

Perhaps I should re-arrange the Raw Data with colour in order to explain what I what ..

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 68px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">RAW DATA </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt">Items </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt" colSpan=5>Vendor Nos</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">A1001 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">A1002 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 8pt">A1003 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 8pt">B1001 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">B1002 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 8pt">B1003 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-SIZE: 8pt">C1010 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">C1011 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 8pt">C1012 </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">SEARCH RESULT </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold">Vendor Nos</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt" rowSpan=6>Items </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">A1001 </TD><TD style="FONT-SIZE: 8pt">A1001 </TD><TD style="FONT-SIZE: 8pt">A1001 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">A1002 </TD><TD style="FONT-SIZE: 8pt">A1002 </TD><TD style="FONT-SIZE: 8pt">A1002 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">B1002 </TD><TD style="FONT-SIZE: 8pt">B1001 </TD><TD style="FONT-SIZE: 8pt">A1003 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 8pt">C1011 </TD><TD style="FONT-SIZE: 8pt">B1003 </TD><TD style="FONT-SIZE: 8pt">B1002 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt">C1011 </TD><TD style="FONT-SIZE: 8pt">C1010 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt">C1012 </TD><TD style="FONT-SIZE: 8pt">C1012 </TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR></TBODY></TABLE>
Ok ... What I want is when I key in the vendor nos in row D20 -
a formula in D21 & so on ... is able to use the reference in D20 to extract the items from the raw data.
Hope this is clear to u ...
cheers
Click to expand...

Assuming that a vendor number occurs just once if at all per item record in the raw data...

D21, control+shift+enter, not just enter, and copy across then down:
Code: 
=IFERROR(INDEX($C$7:$C$15,SMALL(IF($D$7:$H$15=D$20,
    ROW($C$7:$C$15)-ROW($C$7)+1),ROWS(D$21:D21))),"")

Note that IFERROR is not available on systems prior to Excel 2007.

On earler systems...

D19, just enter and copy across:
Code: 
=COUNTIF($D$7:$H$15,D20)

D21, control+shift+enter, not just enter, and copy across then down:
Code: 
=IF(ROWS(D$21:D21)<=D$19,INDEX($C$7:$C$15,
   SMALL(IF($D$7:$H$15=D$20,ROW($C$7:$C$15)-ROW($C$7)+1),
    ROWS(D$21:D21))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
88
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
B
  • Question Question
Search partial text in a lookup
Replies
1
Views
309
felixstraube
F

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top