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

Kane
  • 1
  • 3
  • 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. – Joe Jul 25 '14 at 19:54
  • What do you mean by columns B through (C - MM)? – C.J. Jul 25 '14 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) – Kane Jul 25 '14 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.... – marc_s Jul 26 '14 at 07: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. – Kane Jul 26 '14 at 16:56

2 Answers2

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.

spioter
  • 1,829
  • 1
  • 13
  • 19
  • Excel did not like the formula at RC[-1] or at Table1. – Kane Jul 26 '14 at 16:57
  • 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. – spioter Jul 26 '14 at 20:53
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.

Community
  • 1
  • 1
Kane
  • 1
  • 3