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

[kznmrexcel]

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:

A	B
Barcode CB2016GV0002	<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.

<tbody>
</tbody>

What we have now:

	A	B	C	D	E	F	G
1	CB2016	Google Chromebook
2	Chromebook 2016 hp
3		Copy Number:	2	Vendor:	None	Accession Date:	16-Sep-16
4		Barcode:	CB2016GV0002	Purchase Cost:	250	Collection:	GVMS
5		Copy policy:	Chromebook	Replacement Cost:	250	Location:
6		Condition:	Unknown
7		Notes:	Discarded from inventory on Oct 27, 2017.
8
9
10	CB2016	Google Chromebook
11	Chromebook 2016 hp
12		Copy Number:	10	Vendor:	None	Accession Date:	16-Sep-16
13		Barcode:	CB2016GV0010	Purchase Cost:	250	Collection:	GVMS
14		Copy policy:	Chromebook	Replacement Cost:	250	Location:
15		Condition:	Unknown
16		Notes:	Discarded from inventory on May 12, 2017.

<tbody>
</tbody>

<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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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>

 

[Snackan1986]

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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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>

 

[kznmrexcel]

That sounds fabulous! I don't know if it matters, but I'm on a Mac and using Excel 2011.

 

[lrobbo314]

Create a sheet called "Results" and try the following 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.

 

[kznmrexcel]

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]
   
   [/FONT]
   [FONT=&quot]If the Developer tab is not available[/FONT]
   
   1. [FONT=&quot]On the right side of the ribbon, click
      
      , 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]

 

[lrobbo314]

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.

 

[kznmrexcel]

Thanks!