Solved

Transposing SQL Results in Excel

Posted on 2015-02-10
10
108 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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