We help IT Professionals succeed at work.

Help with Excel

154 Views
Last Modified: 2017-04-09
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
Comment
Watch Question

Author

Commented:
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.

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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Interpreting the question, end result will look like:

Column B                                          Column C
CALEXICO, CA / AB / 1234 / $100      COMPTON, CO / BC / 2987 / $104
CALEXICO, CA / AB / 5678 / $129      COMPTON, CO / BC / 1234 / $107
CALEXICO, CA / AB / 1256 / $135      COMPTON, CO / AB / 5678 / $115
                                                                  COMPTON, CO / TL / 1256 / $136
                                                                  COMPTON, CO / TL / 2525 / $151

From latest comment
I need it to start a new row

I think you mean start a new column.

Author

Commented:
Possibly, I was really wanting to start a new row for Compton at B4.

But if I can get it to column C, I'd be happy
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
do you need formula solution or VBA?

Author

Commented:
I am not "well versed" 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.
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
Neeraj, already provided you with VBA solution.
but if you prefer formula, let me know.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Possibly, I was really wanting to start a new row for Compton at B4.

But if I can get it to column C, I'd be happy

Still doesn't quite make sense.

Calexico is in column B rows 1 to 3
Starting Compton at B4 would then just be a repeat of column A.

Author

Commented:
ProfessorJimJam - please provide formula solution

Author

Commented:
Rob - I know I'm confusing the topic. Let me try again.

Let's say the raw data is in Col A 1-8
Calexico is in A 1-3
Compton is in A 4-8

So once it's transformed
Calexico will be in B1, C1, D1
Compton is in B2, C2, D2, E2, F2
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
So copied and transposed as per first suggestion. Your comment suggested that you don't want it in one row per city.

Author

Commented:
Sorry. Since there will be thousands of rows of data, copy and transpose is not an option.
Rob HensonFinance Analyst
CERTIFIED EXPERT

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

Author

Commented:
Cell B1 - CALEXICO, CA / AB / 1234 / $100
Cell C1 - CALEXICO, CA / AB / 5678 / $129      
Cell D1 - CALEXICO, CA / AB / 1256 / $135      
Cell B2 - COMPTON, CO / BC / 2987 / $104
Cell C2 - COMPTON, CO / BC / 1234 / $107
and so on

Author

Commented:
Great job, not sure I understand what is happening with VB, but it works. Thank you so much.

Author

Commented:
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), ",") - 1)) = dict.Item(Left(x(i, 1), InStr(x(i, 1), ",") - 1)) & "^" & x(i, 1)

Since I don't know VB, can you tell me how to fix?
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

Commented:
Got it, thank you.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.