Brain teaser, VBA code needed to prevent insanity, vlook up index and match

R

Russellsky

New Member
Joined
Aug 31, 2015
Messages
12
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="width: 381"]
<colgroup><col><col><col><col></colgroup><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="width: 767"]
<colgroup><col><col span="6"></colgroup><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]S1132634[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
You must log in or register to reply here.

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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