Brain teaser. Please I need help anybody. My vba coding nightmare

[Russellsky]

I have been playing around for a while now but no matter how hard I try I just can't get my vba coding to work for what I'm trying to do. I have had some limited success with match and index and then removing duplicates witch seemed to give me almost what I needed.
Ok
so this is what I'm trying to do. I have a item number in coloum A. In coloum B There is a also a item. There is spacer number that is found inbetween
the items in coloum A and B .What makes it tricky is there are up to 4 sides
to the item in colume A. And also there is a possibility that there might be up to
two different spacers per item and iten numbers arnt in order

A. B. C.
14. 15. Xcv
15. 14. Zen
15. 16. Nui
13. 15. Agh
12. 15. Lob
17. 16. Yip
17. 18. Why


what I need to get out is coloum A and a list of all the items B C D E are The adjacent items

A. B. C. D. E.
15. 14. 12. 16. 13.
16. 17. 18
18. 17.
14. 15.
12. 15
17. 16
18. 17


If if anybody has any idea how to create a macro to do this. please please help. Im manually doing this at the moment and have 30000 to still do

 

[ExcelGuyDude]

Please review your question and be more clear with what you are wanting. Also give a specific example of the cell values before and after. This explanation is very confusing with all the grammatical errors, but even worse with an example that is hard to read and doesn't show real data that you're using.

Below are some helpful guides to help you make your posts more meaningful, and a less waste of a time for those trying to help answer your question.

 

[Russellsky]

so sorry i have tried to re word my question. apologies for my poor language skills as well English is not my first language

Firstly a massive thank you anybody who is wiling to help me.....

my problem starts with information that has been given to me is very limited but should be enough to solve my problem
i need to identify how many adjacent structures each structure has, basically there will always be one or up to seven
the information given to me is also not in any order so one structure might appear multiple times in one column or alternate
in columns as the sequence is followed, also there might up to maximum of 2 bays between structures

so this is what i have been able to do so far, on sheet two i have merged the two coloums and removed duplicates to give me
a list of all the structures, i have then duplicated information on sheet one but switched the structure ids information for the
duplicated information so that Bay still has the correct structures assigned to it
i have then tried to use index and match back to the first sheet to find the adjacent structures and remove
duplicate structures. in the end i just made a massive stuff up of it and started doing it manually, only problem with that is i have
30 000 to do like this,

please if any body can find it in their heart to help


[TABLE="class: cms_table, width: 381"]
<tbody>[TR]
[TD]BAY[/TD]
[TD]Structure 1 Pick ID[/TD]
[TD]Structure 2 Pick ID[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256315[/TD]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256314[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256313[/TD]
[TD]S1031857[/TD]
[TD]S1031858[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256312[/TD]
[TD]S1031858[/TD]
[TD]S1031859[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256311[/TD]
[TD]S1031859[/TD]
[TD]S1031860[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256310[/TD]
[TD]S1031860[/TD]
[TD]S1031861[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256309[/TD]
[TD]S1031861[/TD]
[TD]S1031862[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1076211[/TD]
[TD]S1031862[/TD]
[TD]S1830436[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1489045[/TD]
[TD]S1032012[/TD]
[TD]S2280348[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B254651[/TD]
[TD]S1039241[/TD]
[TD]S1039242[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1076015[/TD]
[TD]S1039242[/TD]
[TD]S1830240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256692[/TD]
[TD]S1082299[/TD]
[TD]S1082300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1076257[/TD]
[TD]S1082300[/TD]
[TD]S1830478[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B254588[/TD]
[TD]S1093398[/TD]
[TD]S1093399[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1076013[/TD]
[TD]S1093398[/TD]
[TD]S1830230[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256949[/TD]
[TD]S1093411[/TD]
[TD]S1093413[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1379983[/TD]
[TD]S1093411[/TD]
[TD]S1909383[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256951[/TD]
[TD]S1093412[/TD]
[TD]S1093414[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256950[/TD]
[TD]S1093412[/TD]
[TD]S1093413[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1512033[/TD]
[TD]S1093414[/TD]
[TD]S2352540[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256605[/TD]
[TD]S1132631[/TD]
[TD]S1132632[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B256606[/TD]
[TD]S1132632[/TD]
[TD]S1132634[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




this the format i need to get it to, i have manually done the first 2


[TABLE="class: cms_table, width: 767"]
<tbody>[TR]
[TD]Structure 1 Pick ID[/TD]
[TD]ADJ Structure 1[/TD]
[TD]ADJ Structure 2[/TD]
[TD]ADJ Structure 3[/TD]
[TD]ADJ Structure 4[/TD]
[TD]ADJ Structure 5[/TD]
[TD]ADJ Structure 6[/TD]
[/TR]
[TR]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031856[/TD]
[TD]S1031855[/TD]
[TD]S1031857[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031857[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031858[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031859[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031860[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031861[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1031862[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1032012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1039241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1039242[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1082299[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1082300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093398[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093411[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093412[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093414[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1132631[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1132632[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1830436[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S2280348[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1830240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1830478[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093399[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1830230[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1093413[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1909383[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S2352540[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]S1132634[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Apart from the fact that your post makes absolutely no sense to me...

It is a duplicate post, which we really wish you wouldn't do as it causes confusion.....you might end up with 2 people working on the same problem at the same time !!!

http://www.mrexcel.com/forum/excel-...ed-prevent-insanity-vlook-up-index-match.html

 

[Russellsky]

how can i remove it?

 

[Michael M]

You can't
I'll post back to the other thread and advise people to come across to this one !!

 

[Russellsky]

thank you

 

[hiker95]

this the format i need to get it to, i have manually done the first 2

Russellsky,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. What is the raw data worksheet name that contains the following?

Excel 2007
	A	B	C
1	BAY	Structure 1 Pick ID	Structure 2 Pick ID
2	B256315	S1031855	S1031856
3	B256314	S1031856	S1031857
4	B256313	S1031857	S1031858
5	B256312	S1031858	S1031859
Sheet1

4. What is the resulting worksheet name that contains the following?

Excel 2007
	A	B	C	D	E	F	G	H
1	Structure 1 Pick ID	ADJ Structure 1	ADJ Structure 2	ADJ Structure 3	ADJ Structure 4	ADJ Structure 5	ADJ Structure 6
2	S1031855	S1031856
3	S1031856	S1031855	S1031857
4	S1031857
5	S1031858
6	S1031859
7	S1031860
8	S1031861
9	S1031862
10	S1032012
11	S1039241
12	S1039242
13	S1082299
14	S1082300
15	S1093398
16	S1093411
17	S1093412
18	S1093414
19	S1132631
20	S1132632
21	S1830436
22	S2280348
23	S1830240
24	S1830478
25	S1093399
26	S1830230
27	S1093413
28	S1909383
29	S2352540
30	S1132634
31
Results

Interesting challenge.

In order to better understand the logic for the results, please manually complete the results grid, and, post your completed screenshot.

 

[Russellsky]

thank you for trying to help,

1)i work both with office 365 and 2010

2)windows 2007

3)the sheet name with raw data is called BAYS

4)the results sheet is is called ADJ

Maybe if i explain what the data actually is it might help make more sense as-well. structures are actual power poles, bays are the power lines between the poles, some poles will have two bays between them.
the only data that shows any kind of links between poles is the data i have been given and what they want back is a list of all the Poles(the easy part) and the poles that would be adjacent them.

i have almost been able to solve the problem my self

this is the steps i took, i have made a new list of raw data to shown how structure ids are in no specif order

1)create a new sheet called List, make a list of the structures. combination of column B and C, then remove duplicates

Structure 1 Pick ID

<tbody>
</tbody>

</td>[TD]Structure 2 Pick ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
</tr>[TR]
[TD="align: center"]2[/TD]
[TD]B256315[/TD]
[TD]S1031855[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]B256314[/TD]
[TD]S1031857[/TD]
[TD]S1031856[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B256313[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B256312[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"]S1031859[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B366476[/TD]
[TD="align: right"]S1031855[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]B785685[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]B235554[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]B574574[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"]S1031859[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody></table>Excel 2007

<tbody>
<td 0px;="" padding:="" 0.5em;="" border:="" 1px="" solid="" black;"="">Structure 1 Pick ID
</td>
</tbody>

</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">Structure 2 Pick ID</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;"></td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black; text-align: right;"></td></tr>[TR]
[TD="align: center"]10[/TD]
[TD]B256315[/TD]
[TD]S1031856[/TD]
[TD="align: right"]S1031855[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]B256314[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]B256313[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]B256312[/TD]
[TD="align: right"]S1031859[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]B366476[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"]S1031855[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]B785685[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]B235554[/TD]
[TD="align: right"]S1031857[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]B574574[/TD]
[TD="align: right"]S1031859[/TD]
[TD="align: right"]S1031858[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody></table>

3) this is about the point where i went of the rails. i recorded a macro to cut row 3 on sheet called list, and paste it on a new work sheet. then i used index and match back to the sheet with the given data and the duplicated data. this would work like a vlookup but continue to bring back all matches and paste them in column D, i then would remove duplicates in column D and transpose data so they run left to right not up and down. i run this macro until there is no data left on the sheet with just the pole numbers

S1031855

[TH]
B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
</tr></thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;"></td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;"></td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black;">
</td><td style="margin: 0px; padding: 0.5em; border: 1px solid black; text-align: right;"></td></tr>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

4) i then run a macro to merge all the work sheets
and only some times it work but mostly it doesn't

here is the completed sheet

Excel 2007

	A	B	C	D	E	F	G	H
Structure 1 Pick ID	ADJ Structure 1	ADJ Structure 2	ADJ Structure 3	ADJ Structure 4	ADJ Structure 5	ADJ Structure 6
S1031855	S1031856
S1031856	S1031855	S1031857
S1031857
S1031858
S1031859

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]S1031858[/TD]
[TD="align: right"]S1031856[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]S1031857[/TD]
[TD="align: right"]S1031859[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]S1031858[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

</div>


i really hope this helps
thank you

 

[Russellsky]

not sure why that happened, step 2 didnt come through either. let me try that again

thank you for trying to help,

1)i work both with office 365 and 2010

2)windows 2007

3)the sheet name with raw data is called BAYS

4)the results sheet is is called ADJ

Maybe if i explain what the data actually is it might help make more sense as-well. structures are actual power poles, bays are the power lines between the poles, some poles will have two bays between them.
the only data that shows any kind of links between poles is the data i have been given and what they want back is a list of all the Poles(the easy part) and the poles that would be adjacent them.

i have almost been able to solve the problem my self

this is the steps i took, i have made a new list of raw data to shown how structure ids are in no specif order

[TABLE="width: 292"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BAY[/TD]
[TD]Structure 1 Pick ID[/TD]
[TD]Structure 2 Pick ID[/TD]
[/TR]
[TR]
[TD]B256315[/TD]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]B256314[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B256313[/TD]
[TD]S1031858[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B256312[/TD]
[TD]S1031858[/TD]
[TD]S1031859[/TD]
[/TR]
[TR]
[TD]B467564[/TD]
[TD]S1031856[/TD]
[TD]S1031855[/TD]
[/TR]
[TR]
[TD]B456886[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B475757[/TD]
[TD]S1031858[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B575757[/TD]
[TD]S1031859[/TD]
[TD]S1031858[/TD]
[/TR]
</tbody>[/TABLE]

1)create a new sheet called List, make a list of the structures. combination of column B and C, then remove duplicates

[TABLE="width: 132"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Structure 1 Pick ID[/TD]
[/TR]
[TR]
[TD]S1031855[/TD]
[/TR]
[TR]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD]S1031859[/TD]
[/TR]
[TR]
[TD]S1031857[/TD]
[/TR]
</tbody>[/TABLE]

2)i then duplicate the data on the sheet called Bays but swap the pick id numbers around but only for the duplicated data , i have done this as some poles don't appear in column B due to the power line ending on that pole and only being in colomn C

[TABLE="width: 292"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BAY[/TD]
[TD]Structure 1 Pick ID[/TD]
[TD]Structure 2 Pick ID[/TD]
[/TR]
[TR]
[TD]B256315[/TD]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]B256314[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B256313[/TD]
[TD]S1031858[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B256312[/TD]
[TD]S1031858[/TD]
[TD]S1031859[/TD]
[/TR]
[TR]
[TD]B467564[/TD]
[TD]S1031856[/TD]
[TD]S1031855[/TD]
[/TR]
[TR]
[TD]B456886[/TD]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B475757[/TD]
[TD]S1031858[/TD]
[TD]S1031857[/TD]
[/TR]
[TR]
[TD]B575757[/TD]
[TD]S1031859[/TD]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD]B256315[/TD]
[TD]S1031856[/TD]
[TD]S1031855[/TD]
[/TR]
[TR]
[TD]B256314[/TD]
[TD]S1031857[/TD]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]B256313[/TD]
[TD]S1031857[/TD]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD]B256312[/TD]
[TD]S1031859[/TD]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD]B467564[/TD]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]B456886[/TD]
[TD]S1031857[/TD]
[TD]S1031856[/TD]
[/TR]
[TR]
[TD]B475757[/TD]
[TD]S1031857[/TD]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD]B575757[/TD]
[TD]S1031858[/TD]
[TD]S1031859[/TD]
[/TR]
</tbody>[/TABLE]
3) this is about the point where i went of the rails. i recorded a macro to cut row 3 on sheet called list, and paste it on a new work sheet. then i used index and match back to the sheet called Bays. this would work like a vlookup but continue to bring back all matches and paste them in column D, i then would remove duplicates in column D and transpose data so they run left to right not up and down. i run this macro until there is no data left on the sheet with just the pole numbers

4) i then run a macro to merge all the work sheets
and only some times it work but mostly it doesn't

here is the completed sheet

[TABLE="width: 329"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Structure 1 Pick ID[/TD]
[TD]ADJ 1 Pick ID[/TD]
[TD]ADJ 2 Pick ID[/TD]
[/TR]
[TR]
[TD]S1031855[/TD]
[TD]S1031856[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]S1031856[/TD]
[TD]S1031857[/TD]
[TD]S1031855[/TD]
[/TR]
[TR]
[TD]S1031858[/TD]
[TD]S1031857[/TD]
[TD]S1031859[/TD]
[/TR]
[TR]
[TD]S1031859[/TD]
[TD]S1031858[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]S1031857[/TD]
[TD]S1031856[/TD]
[TD]S1031858[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]

hope this works
thanks