Solved

Transposing SQL Results in Excel

Posted on 2015-02-10
10
114 Views
Last Modified: 2016-02-10
I have two columns of data I'm importing into Excel from SQL.

Column A (Product) has values that will appear 1 to x number of times based on matching results from Column B (Components).

For example:

Product          Component
P1                   C1
P1                   C5
P1                   C8
P1                   C9
P2                   C2
P2                   C3
P3                   C1
P3                   C2
P3                   C3
P4                   C1

Open in new window


What I'm trying to accomplish is to get the components for each product into a horizontal list:

P1     C1     C5     C8     C9
P2     C2     C3
P3     C1     C2     C3
P4     C1

Open in new window


I'm dealing with about 50,000 rows of data.

I'm not sure how to transpose this data. Any suggestions?
0
Comment
Question by:MIGINC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40601250
do you have maximum number of possible columns?
0
 

Author Comment

by:MIGINC
ID: 40601265
I don't think I have any products that are made up of more than 10 components.
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40601387
try this excel vba script

Sub TransposeValues()
Dim TargetValue As String
Dim LastRow As Integer, Targetrow, TargetCol, ii
LastRow = ActiveSheet.UsedRange.Rows.Count
Targetrow = 2
TargetValue = Range("A2").Value
TargetCol = 2
 For ii = 3 To LastRow + 1
  If Cells(ii, 1) = TargetValue Then
  TargetCol = TargetCol + 1
   Cells(Targetrow, TargetCol) = Cells(ii, 2)
  Cells(ii, 3) = "Delete"
  Else
  Targetrow = ii
  TargetCol = 2
  TargetValue = Cells(ii, 1)
  End If
 Next
 For ii = LastRow + 1 To 1 Step -1
 If Cells(ii, 3) = "Delete" Then
 Rows.EntireRow(ii).Delete
 End If
 Next
End Sub

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:MIGINC
ID: 40601494
I get a popup window titled "Microsoft Visual Basic for Applications" with the error message "Overflow".

I grabbed a section of items instead of the whole list and it seems to work. I could split up the worksheet into multiple sheets if I need to, but shouldn't Excel be able to handle 50,000 rows?

Another question: It just so happened that one of the items I grabbed actually has 10 components. Is there a limit in this code that it's only going to find up to 10? I only ask because of your first question. I don't see anything in your code that would limit it, but I'm not a VBA guy.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601544
no column limit, I asked the question because I had a none vba solution in mind, and found it needs a lot of user intervention like sorting, deleting, and found vba will be better
for overflow issue try to replace all integer datatype to long rhis will solve 32k limit

Open in new window

0
 

Author Comment

by:MIGINC
ID: 40601566
I'm sorry - I don't understand what you mean by converting integer to long. My 2 columns are just text columns.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601575
in vba change
dim anyvar as integer
to
as long
0
 

Author Comment

by:MIGINC
ID: 40601582
That did the trick.

Thanks for your help!
0
 

Author Closing Comment

by:MIGINC
ID: 40601584
Excellent response time and great follow-up help!
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601599
you are welcome, and thanks a lot for this encouraging feedback
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question