Multiple cells in columns moved to same row, different cells?

K

kznmrexcel

Board Regular
Joined
Jun 16, 2010
Messages
86
Office Version
  1. 2016
Platform
  1. MacOS
Hi, everyone,
I use library software that outputs a report as a text file with the information below for each Chromebook (item) that has been discarded in our system due to damage. These lines come into Excel on different rows in the same column. I need this information on a single row for each item in Excel so that we can sort the information by date. All I actually need is the "Barcode: CB2016GV0002" and the "Discarded from inventory on Oct 27, 2017" on the same row, as the other items on this report are not needed. However, putting everything from this record on one row would allow me to later delete what I don't need. Help, please!
Thanks,
Karen

What we hope to achieve:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Barcode CB2016GV0002[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style>Discarded from inventory on Oct 27, 2017.[/TD]
[/TR]
</tbody>[/TABLE]


What we have now:
[TABLE="class: grid, width: 118%"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="colspan: 2"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CB2016[/TD]
[TD="colspan: 3"]Google Chromebook[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 2"]Chromebook 2016 hp[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Copy Number:[/TD]
[TD="colspan: 2"]2[/TD]
[TD]Vendor:[/TD]
[TD]None[/TD]
[TD]Accession Date:[/TD]
[TD]16-Sep-16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Barcode:[/TD]
[TD="colspan: 2"]CB2016GV0002[/TD]
[TD]Purchase Cost:[/TD]
[TD]250[/TD]
[TD]Collection:[/TD]
[TD]GVMS[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Copy policy:[/TD]
[TD="colspan: 2"]Chromebook[/TD]
[TD]Replacement Cost:[/TD]
[TD]250[/TD]
[TD]Location:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Condition:[/TD]
[TD="colspan: 2"]Unknown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Notes:[/TD]
[TD="colspan: 5"] Discarded from inventory on Oct 27, 2017.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]CB2016[/TD]
[TD="colspan: 3"]Google Chromebook[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="colspan: 2"]Chromebook 2016 hp[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Copy Number:[/TD]
[TD="colspan: 2"]10[/TD]
[TD]Vendor:[/TD]
[TD]None[/TD]
[TD]Accession Date:[/TD]
[TD]16-Sep-16[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Barcode:[/TD]
[TD="colspan: 2"]CB2016GV0010[/TD]
[TD]Purchase Cost:[/TD]
[TD]250[/TD]
[TD]Collection:[/TD]
[TD]GVMS[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Copy policy:[/TD]
[TD="colspan: 2"]Chromebook[/TD]
[TD]Replacement Cost:[/TD]
[TD]250[/TD]
[TD]Location:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Condition:[/TD]
[TD="colspan: 2"]Unknown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]Notes:[/TD]
[TD="colspan: 5"] Discarded from inventory on May 12, 2017.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I can create a macro for you. Give me a few minutes
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>
 
Upvote 0
That sounds fabulous! I don't know if it matters, but I'm on a Mac and using Excel 2011.
 
Upvote 0
Create a sheet called "Results" and try the following code.

Code: 
Sub test()
Dim LR As Long
Dim ws As Worksheet
Dim wMain As Worksheet
Dim r As Range
Dim fVal As Range
Dim fAddress As String
Set wMain = Sheets("Sheet5") 'Change to your sheet with data in it
Set ws = Sheets("Results")
LR = wMain.Range("B" & Rows.Count).End(xlUp).Row()
Set r = wMain.Range("B1:B" & LR)
With r
    Set fVal = .Find("Barcode:", LookIn:=xlValues)
    fAddress = fVal.Address
    Do
        ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = fVal.Offset(, 1).Value
        ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = fVal.Offset(3, 1).Value
        Set fVal = .FindNext(fVal)
    Loop While Not fVal Is Nothing And fVal.Address <> fAddress
End With
End Sub

Not sure if you will have issues being on a Mac.
 
Upvote 0
I tried this and got all the way to closing the module using these steps. On the sheet called Results, it's just blank. I'm not sure if, on the Mac, I'm supposed to use the Command key instead of the Control key. Would I first select cell A1 and then type either Ctrl+Shift+Enter or Cmd+Shift+Enter? Here are the steps I followed to paste the module:


  1. [FONT=&quot]On the Developer tab, under Visual Basic, click Editor.[/FONT]
    [FONT=&quot]
    d79fe91f-d6c1-4520-bc4f-d1edff565697.gif
    [/FONT]
    [FONT=&quot]If the Developer tab is not available[/FONT]
    1. [FONT=&quot]On the right side of the ribbon, click
      6f90d5de-1172-4076-99f0-93b09b2da959.gif
      , and then click Ribbon Preferences.[/FONT]
    2. [FONT=&quot]Under Customize, select the Developer check box.[/FONT]
  2. [FONT=&quot]If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.[/FONT]
  3. [FONT=&quot]In the code window of the module, type or paste the macro code that you want to use.[/FONT]
  4. [FONT=&quot]When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.[/FONT]
 
Upvote 0
I'm not sure what you're talking about with Ctrl+Shift+Enter. Did you read what was in my signature? That's just a general tip that shows up in all of my posts. Doesn't have anything to do with this.

I copied your data into a worksheet. My sheet is called Sheet5, but if yours is different then you'll need to change the sheet name in the code.
Then I created a sheet called results. In A1 I put "Barcode" and B1 I put "Notes"

Then, you should access the editor and paste the code. Then remember to change the line of code where it sets wMain to 'Sheet5'.

Then it should just be a matter of running the macro. There should be a play button in the editor. In windows, if you exit the editor and are looking at your sheet, if you hit Alt+F8, it will bring up a list of all the macros you have available to run. Not sure what the Mac equivalent is.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
C
  • Question Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
M
  • Question Question
Multiple table array
Replies
5
Views
938
Fluff
Fluff
M
  • Question Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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