VBA - fine data from a column with huge information

I

iris1007

New Member
Joined
Jun 28, 2017
Messages
31
Hi All,

I have huge data which I need to search changed parts from a same column and copy/paste to new column. Hope to use the VBA t search/copy/paste it.
Attached is the sample.
Thanks in advance for the helps.

[TABLE="width: 1213"]
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="485" style="width: 364pt; mso-width-source: userset; mso-width-alt: 17237;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4067;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2787;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2844;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2474;"> <col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="width: 94, bgcolor: transparent"]Part #[/TD]
[TD="width: 485, bgcolor: transparent"]Remarks[/TD]
[TD="width: 90, bgcolor: transparent"]P1[/TD]
[TD="width: 72, bgcolor: transparent"]P2[/TD]
[TD="width: 114, bgcolor: transparent"]P3[/TD]
[TD="width: 78, bgcolor: transparent"]P4[/TD]
[TD="width: 76, bgcolor: transparent"]P5[/TD]
[TD="width: 80, bgcolor: transparent"]P6[/TD]
[TD="width: 72, bgcolor: transparent"]P7[/TD]
[TD="width: 70, bgcolor: transparent"]P8[/TD]
[TD="width: 64, bgcolor: transparent"]P9[/TD]
[TD="width: 64, bgcolor: transparent"]P10[/TD]
[TD="width: 64, bgcolor: transparent"]P11[/TD]
[TD="width: 64, bgcolor: transparent"]P12[/TD]
[TD="width: 64, bgcolor: transparent"]P13[/TD]
[TD="width: 64, bgcolor: transparent"]P14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A123456[/TD]
[TD="width: 485, bgcolor: transparent"]Condition as Received : good
 Evaluations : Pre-test found leakage, dirty and corroded. Unit to be dismantled for rectification.

1 MD14583-110 CYLINDER 1 ALL found worn.
2 770404451 Shorted.
3 17421422A-11234 ASSY 1 found faulty
 4 S010702854 GENERATOR 1 deteriorated.
5 762547ZDG STATORDamaged

1 170125C700 1 Seized
2 778661868 PIN 1 Cracked
3 7618900-4 SHAFT 1 Bent
4 761145-870 SHAFT 1 Bent
5 1171895 SHAFT 1 Bent
6 554878241G1 Scored
7 760-144635 Worn
8 MCE6181880 GUIDE 1 Cracked
9 4480-100-1879 SHAFT 2 Seized
10 AB54761878 Worn
11 1879-584-11720 RETAINER 1 Scored
12 1872-100-22458HD 1 Worn
13 G5495 WASHER 4 Worn
14 11924581 BEARING 1 Cracked
15 7478-A125-100 GEAR 2 Seized
16 590514867 FILTER 2
17 744718314-1 BEARING 1 Worn
18 788472-886 RING 1 Worn
19 93-8-100-2245A TUBE 1 Scored
20 3311-6 BEARING 2 Cracked
21 885444157-01 RING 1
22 76-554381 WEAR PLATE 6 Worn
23 186311445 PLUG 1 Seized
24 58355021 WASHER 6 Worn
25 A6563 INDICATR 1
26 55478-115487
27 NAS33456CN622R WASHER 3 Worn
28 761145206 DECAL CPN:2413227 1
29 MS376100-2216 SCREW 3 Worn[/TD]
[TD="bgcolor: transparent"]MD14583-110[/TD]
[TD="bgcolor: transparent"]770404451[/TD]
[TD="bgcolor: transparent"]17421422A-11234[/TD]
[TD="bgcolor: transparent"]S010702854[/TD]
[TD="bgcolor: transparent"]762547ZDG[/TD]
[TD="bgcolor: transparent"]170125C700[/TD]
[TD="bgcolor: transparent"]778661868[/TD]
[TD="bgcolor: transparent"]7618900-4[/TD]
[TD="bgcolor: transparent"]761145-870[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
I do not understand any of this:

Tell us what column to search like column B or column C

And search for what

And do what when value is found
 
Upvote 0
Hi,
Thanks for the fast respond.
1. Search data in column B
2. Find the parts. The parts contained number+ alphabets + "-"
3. When value found, paste each difference value to column C, D, E..........


Thanks
 
Upvote 0
That does not help me.
Your saying if any value in column B has a letter a number or a dash copy to next column

So give me a example of something that would not be copied.
 
Upvote 0
Hi,
For data below..
[TABLE="width: 435"]
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="485" style="width: 364pt; mso-width-source: userset; mso-width-alt: 17237;"> <tbody>[TR]
[TD="width: 94, bgcolor: transparent"]Part #[/TD]
[TD="width: 485, bgcolor: transparent"]Remarks[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A123456[/TD]
[TD="width: 485, bgcolor: transparent"]Condition as Received : good
 Evaluations : Pre-test found leakage, dirty and corroded. Unit to be dismantled for rectification.

1 MD14583-110 CYLINDER 1 ALL found worn.
2 770404451 Shorted.
3 17421422A-11234 ASSY 1 found faulty
 4 S010702854 GENERATOR 1 deteriorated.
5 762547ZDG STATORDamaged

1 170125C700 1 Seized
2 778661868 PIN 1 Cracked
3 7618900-4 SHAFT 1 Bent
4 761145-870 SHAFT 1 Bent
5 1171895 SHAFT 1 Bent
6 554878241G1 Scored
7 760-144635 Worn
8 MCE6181880 GUIDE 1 Cracked
9 4480-100-1879 SHAFT 2 Seized
10 AB54761878 Worn
11 1879-584-11720 RETAINER 1 Scored
12 1872-100-22458HD 1 Worn
13 G5495 WASHER 4 Worn
14 11924581 BEARING 1 Cracked
15 7478-A125-100 GEAR 2 Seized
16 590514867 FILTER 2
17 744718314-1 BEARING 1 Worn
18 788472-886 RING 1 Worn
19 93-8-100-2245A TUBE 1 Scored
20 3311-6 BEARING 2 Cracked
21 885444157-01 RING 1
22 76-554381 WEAR PLATE 6 Worn
23 186311445 PLUG 1 Seized
24 58355021 WASHER 6 Worn
25 A6563 INDICATR 1
26 55478-115487
27 NAS33456CN622R WASHER 3 Worn
28 761145206 DECAL CPN:2413227 1
29 MS376100-2216 SCREW 3 Worn[/TD]
[/TR]
</tbody>[/TABLE]

I need the below value to be copied..
[TABLE="width: 364"]
<colgroup><col width="485" style="width: 364pt; mso-width-source: userset; mso-width-alt: 17237;"> <tbody>[TR]
[TD="width: 485, bgcolor: transparent"]MD14583-110
 770404451
17421422A-11234
S010702854.
762547ZDG
170125C700
778661868
7618900-4
761145-870
1171895
554878241G1
760-144635
MCE6181880
4480-100-1879
AB54761878
1879-584-11720
1872-100-22458HD
G5495
11924581
7478-A125-100
590514867
744718314-1
788472-886
93-8-100-2245A
3311-6
885444157-01
76-554381
186311445
58355021
A6563
55478-115487
NAS33456CN622R
761145206
MS376100-2216[/TD]
[/TR]
</tbody>[/TABLE]

But, difference parts in column A may have difference value in column B.
Thanks
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
M
  • Question Question
Index Match formula with multiple criteria
Replies
5
Views
783
DanteAmor
DanteAmor
S
  • Question Question
pivot table calculated field - by broken out values in a column
Replies
1
Views
650
smakatura
S
R
  • Question Question
Copy rows from sheet1 to sheet2 based on date.
Replies
2
Views
874
MARK858
MARK858
J
  • Question Question
IFANDSUM Questions
2
Replies
12
Views
2K
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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