<div>
thank you, but I'm looking for a way to do this as part of an automated report. the problem with transpose is it just strings the cities along in the same row and I need it to start a new row when the city changes. <br />
<br />
when i say automated, i mean i have a data extract that i will copy and paste into this spreadsheet for formatting. There will be thousands of rows of data coming in.
</div>


<div>
Interpreting the question, end result will look like:<br />
<br />
Column B &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Column C<br />
CALEXICO, CA / AB / 1234 / $100 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / BC / 2987 / $104<br />
CALEXICO, CA / AB / 5678 / $129 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / BC / 1234 / $107<br />
CALEXICO, CA / AB / 1256 / $135 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / AB / 5678 / $115<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / TL / 1256 / $136<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / TL / 2525 / $151<br />
<br />
From latest comment 
<blockquote>
I need it to start a new row
</blockquote>
<br />
I think you mean start a new column.
</div>


<div>
Possibly, I was really wanting to start a new row for Compton at B4.<br />
<br />
But if I can get it to column C, I'd be happy
</div>


<div>
do you need formula solution or VBA?
</div>


<div>
I am not &quot;well versed&quot; in VBA, so it would take some hand holding. I have a little more experience with formulas but I'm open to trying anything at this point. I've been trying to figure out some type of solution for the last couple of weeks.
</div>


Wayne_TransformData.xlsm

<div>
Neeraj, already provided you with VBA solution.<br />
but if you prefer formula, let me know.
</div>


<div>
<blockquote>
Possibly, I was really wanting to start a new row for Compton at B4.<br />
<br />
But if I can get it to column C, I'd be happy
</blockquote>
<br />
Still doesn't quite make sense.<br />
<br />
Calexico is in column B rows 1 to 3<br />
Starting Compton at B4 would then just be a repeat of column A.
</div>


<div>
ProfessorJimJam - please provide formula solution
</div>


<div>
Rob - I know I'm confusing the topic. Let me try again.<br />
<br />
Let's say the raw data is in Col A 1-8<br />
Calexico is in A 1-3<br />
Compton is in A 4-8<br />
<br />
So once it's transformed <br />
Calexico will be in B1, C1, D1<br />
Compton is in B2, C2, D2, E2, F2
</div>


<div>
So copied and transposed as per first suggestion. Your comment suggested that you don't want it in one row per city.
</div>


<div>
Sorry. Since there will be thousands of rows of data, copy and transpose is not an option.
</div>


<div>
Understood but do you want the result as if copied and transposed or each block/city in a separate column?
</div>


<div>
Cell B1 - CALEXICO, CA / AB / 1234 / $100<br />
Cell C1 - CALEXICO, CA / AB / 5678 / $129 &nbsp; &nbsp; &nbsp;<br />
Cell D1 - CALEXICO, CA / AB / 1256 / $135 &nbsp; &nbsp; &nbsp;<br />
Cell B2 - COMPTON, CO / BC / 2987 / $104<br />
Cell C2 - COMPTON, CO / BC / 1234 / $107<br />
and so on
</div>


<div>
Great job, not sure I understand what is happening with VB, but it works. Thank you so much.
</div>


<div>
Neeraj - I've added more data to my document. When I run the code, I get a Run-time error '5': Invalid procedure call or argument on this dict.Item(Left(x(i, 1), InStr(x(i, 1), &quot;,&quot;) - 1)) = dict.Item(Left(x(i, 1), InStr(x(i, 1), &quot;,&quot;) - 1)) &amp;&nbsp;&quot;^&quot; &amp;&nbsp;x(i, 1)<br />
<br />
Since I don't know VB, can you tell me how to fix?
</div>


<div>
The first word followed by a comma is used as a key identifier for a record so if a record doesn't contain a comma after the first work, the procedure will fail.
</div>


<div>
<div class="content wysiwyg-content">
I have a row of data that I want to reformat. I can't seem to find a way to do this with formulas.<br />
<br />
Here is what I have - <br />
All in column A, Rows 1-8<br />
CALEXICO, CA / AB / 1234 / $100<br />
CALEXICO, CA / AB / 5678 / $129<br />
CALEXICO, CA / AB / 1256 / $135<br />
COMPTON, CO / BC / 2987 / $104<br />
COMPTON, CO / BC / 1234 / $107<br />
COMPTON, CO / AB / 5678 / $115<br />
COMPTON, CO / TL / 1256 / $136<br />
COMPTON, CO / TL / 2525 / $151<br />
<br />
Here is what I want -<br />
This row is &nbsp;in Cell B1, B2, B3<br />
CALEXICO, CA / AB / 1234 / $100 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALEXICO, CA / AB / 5678 / $129 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALEXICO, CA / AB / 1256 / $135 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
<br />
This row is &nbsp;in Cell C1, C2, C3, C4, C5<br />
COMPTON, CO / BC / 2987 / $104 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / BC / 1234 / $107 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / AB / 5678 / $115 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / TL / 1256 / $136 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMPTON, CO / TL / 2525 / $151<br />
<br />
Any help would be greatly appreciated
</div>
</div>


I have a row of data that I want to reformat. I can't seem to find a way to do this with formulas.

Here is what I have - 
All in column A, Rows 1-8
CALEXICO, CA / AB / 1234 / $100
CALEXICO, CA / AB / 5678 / $129
CALEXICO, CA / AB / 1256 / $135
COMPTON, CO / BC / 2987 / $104
COMPTON, CO / BC / 1234 / $107
COMPTON, CO / AB / 5678 / $115
COMPTON, CO / TL / 1256 / $136
COMPTON, CO / TL / 2525 / $151

Here is what I want -
This row is  in Cell B1, B2, B3
CALEXICO, CA / AB / 1234 / $100	CALEXICO, CA / AB / 5678 / $129	CALEXICO, CA / AB / 1256 / $135		

This row is  in Cell C1, C2, C3, C4, C5
COMPTON, CO / BC / 2987 / $104	COMPTON, CO / BC / 1234 / $107	COMPTON, CO / AB / 5678 / $115	COMPTON, CO / TL / 1256 / $136	COMPTON, CO / TL / 2525 / $151

Any help would be greatly appreciated

Help with Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.