Moving scattered cell data to new column?

[LarryW]

What I have is scattered data fields on a worksheet that are not organized
into one column. There is one common entry into every field but the rest of
the information is in numbers. Example: "PIN 012345" (Pin is in every cell,
but the numbers in every cell are not the same).

I want to move all found PIN data to a new column. Example: Cell rows 1
thru 40,000 all have the PIN entry, but in different columns. I need to
"Shift/Move" all the PIN data to a new column (without multiple commands for
each individual row of data).

Help anyone :-(

 

[Gary''s Student]

The following is just an example that you can adapt for your needs. It
assumes that column K is the destination column. It scans, row-by-row,
columns A thru J looking for cells starting with PIN. IF a cell is found, it
is moved to colum K

Sub pin_master()
Set r = ActiveSheet.UsedRange
n = r.Rows.Count + r.Row - 1
For i = 1 To n
For j = 1 To 10
If Left(Cells(i, j).Value, 3) = "PIN" Then
Cells(i, j).Copy Cells(i, "K")
Cells(i, j).Clear
End If
Next
Next
End Sub

 

[LarryW]

All sounds good Gary. However, where would I even "Start" I'm a Novice at
this and it looks like code here that would have to be generated for the
destination column. I'm sure I can do it, I just need to know step by step.
Thanks

 

[Max]

Here's an alternative worksheet function which could also deliver the results

Assuming "PIN" data is found scattered within cols A to J, data from row1
down, with only 1 instance per row (if any is found), then this array formula
will gather the PIN data in col K

Put in K1's formula bar, then press CTRL+SHIFT+ENTER to confirm the formula
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH("PIN",A1:J1)),0)),"",INDEX(A1:J1,MATCH(TRUE,ISNUMBER(SEARCH("PIN",A1:J1)),0)))
Copy K1 down as far as required

-----
If you want to try out GS's subroutine suggestion, try these steps ..
Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste all lines, inclusive,
from: Sub pin_master()
till: End Sub
into the code window on the right

Press Alt+Q to get back to Excel
In Excel, press Alt+F8 to bring up the Macro dialog
Double click directly on "pin_master" to run the sub
(or select "pin_master" > click Run)

 

[Gary''s Student]

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm