I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

If your data is already sorted by column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hshayh0rn wrote:

I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

--

Dave Peterson

Wow Dave! That worked really well. Now I don't want to be really greedy but
here is the next and final step I need to accomplish. I have another sheet
that contains the following data type:

A B C D

J. Doe Junk Data w377004 AA1 BIY KKI
WQQ
T. Smith Junk Data w377008 TIY NNU FFQ
FFT

I would like to have a macro look at the data in the sheet you just helped
me with looking for the numbers on this sheet in column C and if that number
exists on the other sheet then bring in the data from that sheet and put it
in column E.

We don't need to think about opening that other sheet and looking for the
data. I can have the person using the sheet simply paste the data in it's raw
format into this sheet and run the macro you just helped me with. I think
what I would do though is combine the two sets of code so that it's one
action for the user to complete.

"Dave Peterson" wrote:

If your data is already sorted by column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hshayh0rn wrote:

I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

--

Dave Peterson

I would just fill in the cell with a formula:

(You do have a leading w in column C of this data, though...)

=vlookup(mid(c1,2,255),sheet2!a:b,2,false)
or if those other values are numbers (not numbers treated like text)
=vlookup(--mid(c1,2,255),sheet2!a:b,2,false)

You could even look for errors:

=if(iserror(vlookup(...)),"",vlookup(...))

You can record a macro when you build the formula you want, copy it down the
range and edit|copy, followed by edit|Paste special values.

Then just include that in your original code????


hshayh0rn wrote:

Wow Dave! That worked really well. Now I don't want to be really greedy but
here is the next and final step I need to accomplish. I have another sheet
that contains the following data type:

A B C D

J. Doe Junk Data w377004 AA1 BIY KKI
WQQ
T. Smith Junk Data w377008 TIY NNU FFQ
FFT

I would like to have a macro look at the data in the sheet you just helped
me with looking for the numbers on this sheet in column C and if that number
exists on the other sheet then bring in the data from that sheet and put it
in column E.

We don't need to think about opening that other sheet and looking for the
data. I can have the person using the sheet simply paste the data in it's raw
format into this sheet and run the macro you just helped me with. I think
what I would do though is combine the two sets of code so that it's one
action for the user to complete.

"Dave Peterson" wrote:

If your data is already sorted by column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hshayh0rn wrote:

I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

--

Dave Peterson


--

Dave Peterson

When I use the first formula I get a result in the cell I typed the formula
in of: #N/A

"Dave Peterson" wrote:

I would just fill in the cell with a formula:

(You do have a leading w in column C of this data, though...)

=vlookup(mid(c1,2,255),sheet2!a:b,2,false)
or if those other values are numbers (not numbers treated like text)
=vlookup(--mid(c1,2,255),sheet2!a:b,2,false)

You could even look for errors:

=if(iserror(vlookup(...)),"",vlookup(...))

You can record a macro when you build the formula you want, copy it down the
range and edit|copy, followed by edit|Paste special values.

Then just include that in your original code????


hshayh0rn wrote:

Wow Dave! That worked really well. Now I don't want to be really greedy but
here is the next and final step I need to accomplish. I have another sheet
that contains the following data type:

A B C D

J. Doe Junk Data w377004 AA1 BIY KKI
WQQ
T. Smith Junk Data w377008 TIY NNU FFQ
FFT

I would like to have a macro look at the data in the sheet you just helped
me with looking for the numbers on this sheet in column C and if that number
exists on the other sheet then bring in the data from that sheet and put it
in column E.

We don't need to think about opening that other sheet and looking for the
data. I can have the person using the sheet simply paste the data in it's raw
format into this sheet and run the macro you just helped me with. I think
what I would do though is combine the two sets of code so that it's one
action for the user to complete.

"Dave Peterson" wrote:

If your data is already sorted by column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hshayh0rn wrote:

I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

--

Dave Peterson


--

Dave Peterson

Use the second formula to test for errors.

=if(iserror(vlookup(...)),"",vlookup(...))



hshayh0rn wrote:

When I use the first formula I get a result in the cell I typed the formula
in of: #N/A

"Dave Peterson" wrote:

I would just fill in the cell with a formula:

(You do have a leading w in column C of this data, though...)

=vlookup(mid(c1,2,255),sheet2!a:b,2,false)
or if those other values are numbers (not numbers treated like text)
=vlookup(--mid(c1,2,255),sheet2!a:b,2,false)

You could even look for errors:

=if(iserror(vlookup(...)),"",vlookup(...))

You can record a macro when you build the formula you want, copy it down the
range and edit|copy, followed by edit|Paste special values.

Then just include that in your original code????


hshayh0rn wrote:

Wow Dave! That worked really well. Now I don't want to be really greedy but
here is the next and final step I need to accomplish. I have another sheet
that contains the following data type:

A B C D

J. Doe Junk Data w377004 AA1 BIY KKI
WQQ
T. Smith Junk Data w377008 TIY NNU FFQ
FFT

I would like to have a macro look at the data in the sheet you just helped
me with looking for the numbers on this sheet in column C and if that number
exists on the other sheet then bring in the data from that sheet and put it
in column E.

We don't need to think about opening that other sheet and looking for the
data. I can have the person using the sheet simply paste the data in it's raw
format into this sheet and run the macro you just helped me with. I think
what I would do though is combine the two sets of code so that it's one
action for the user to complete.

"Dave Peterson" wrote:

If your data is already sorted by column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hshayh0rn wrote:

I have a spread sheet that is set-up similar to this:

377004 AA1
377004 BI2
377004 HH6
377008 DFE
377009 AA1
377009 DFE
377025 BI2
377025 CCI

I need a function or some VB code that would create a new cell with the code
in column A only listed once and then the codes in column B concatenated
together (with spaces between each code). It should look something like this:

377004 AA1 BI2 HH6
377008 DFE
377009 AA1 DFE
377025 BI2 CCI

Anyone have some ideas?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson