0

In Excel 2010 or Microsoft SQL Server 2012:

I have a table that has an ID in column a, and then columns B through (C - MM) with locations.

I want to have column A be all of the locations & then column B be the ID.

Any way I can figure out so far has been to transpose by hand.

Any help would be greatly appreciated!

Imaged added: image

Improve this question
5
  • If it's a one time thing, transpose by hand (by paste special->transpose) in Excel seems by far the easiest to me. If it's not, then sql server should be able to pivot it. Commented Jul 25, 2014 at 19:54
  • What do you mean by columns B through (C - MM)? Commented Jul 25, 2014 at 19:57
  • Sometimes i have 1 location for an ID (column B) sometimes i'll have multiple locations for an ID, sometimes hundreds (all the way to column MN in excel) Commented Jul 25, 2014 at 20:13
  • You could post your picture to e.g. www.tinypic.com or any other free image hoster, and post a link here.... Commented Jul 26, 2014 at 7:02
  • Image up on tinypic. Thanks Marc_s for the suggestion. The image is an example. I have over 5000 rows & sometimes hundreds of columns. Commented Jul 26, 2014 at 16:56

2 Answers 2

Reset to default
0

here's a way that's semi-automatic in Excel which will work assuming the ID row is unique (if it isn't unique, you will need to clean that up first)

  1. Assume your original worksheet of data is named "Raw Data"
  2. Convert the raw data into a Table or Named Range - call it Table1
  3. Add a new worksheet, call it "Normal Data" and type "ID" into cell A1 and "Location" into cell B1
  4. Copy the data (no header) from column A (this is the list of IDs) from "Raw Data" into column A of "Normal Data"
  5. Add this formula to column 2 of "Normal Data" as follows, but change "X" to the number of rows of data (excluding header) you have in "Raw Data": =VLOOKUP(A2,Table1,ROUNDDOWN((ROW()-2)/X,0)+2,FALSE)
  6. Make sure this looks correct
  7. Append the entire "List of ID's" a few times to column 1 of "Normal Data" a couple of times
  8. Make sure it still looks correct
  9. If your location data extends to Column MN, then you need 350 total copies of the original ID list.
  10. Save the Excel to something like "Normalize Location with Formula"
  11. Save the excel to something like "Normalized Location Data"
  12. Copy paste values of "Normal Data" to save values only, no formulae
  13. Save the worksheet
  14. Optionally filter the "Normal Data" to rows with blank location data and delete
  15. This is now ready to import to a DB (and if you didn't delete the blanks, you cand just delete them via SQL after you import)

Why this works: Vlookup gets the correct row of raw data, and then the formula dynamically adjusts to the correct column based on the row count.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Excel did not like the formula at RC[-1] or at Table1.
I fixed it. Sorry - my original solution required "RC" notation, but then I figured out a way using the default "column row" notation and didn't update that part of the formula.
0

Pivoting rows into columns in SQL Server

Spioter, thanks for the info. I never tried this as i finally found information about using the UNPIVOT command in SQL.

Link is above.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.