Many Columns return into fewer columns.

[Solved/Closed]
Report
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
-
Posts
47368
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
-
Hello,

I have a spreadsheet that was exported from Survey Monkey. So the format cannot be changed. There can be data in any of 100 columns. However, no one record will have more than 20 columns with data.

I need to build a second spreadsheet that will pull the data into only 20 columns.

Example.
Record 1 has data in the following columns:
A, D, F, AA, DI, and EA

Record 2 has data in the following columns:
G, N, AC, AT, and BG

In my new spreadsheet I want to return the data into these columns:
A, B, C, D, E, and F (on out to a max of 20 columns)

Every record in the original file will be different. No 2 lines are the same. And as I said there are 100 possible columns with 20 Max responses.

This may need to be done with a Macro.

I would appreciate any help and ideas!
Thank you!!

7 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am a bit confuse here. How out of 20 columns one choose 6 columns ? May be if you can upload a sample workbook at some public file share site like speedyshare AND POST BACK LINK TO FILE back here, it might be easy to understand what you are looking for
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013

I will be happy to post something. Maybe I can explain better.

I belong to a club (we show dogs). AKC has about 100 titles a dog can obtain. Call them college degrees for an easier way to understand.

There are 100 POSSIBLE degrees a person can get. However no one really gets 100. They might only get 1. Or they might get 6. OR...... Yet the 100 possible options still exist.

The Excel sheet that comes out of Survey Monkey will drop 100 degree options into 100 columns - 1 for each possible degree. If I get the first degree - then it would be in column 1. If I get the 90th degree - it will land in column 90. I have no issue looking at the raw data and seeing that there is a degree listed in column 90.

My problem becomes - I need to get this into one report. That is NOT 100 columns wide.

Let's say your degree is a Math Degree and you (and everyone else that got a Math Degree) have a notation in Column 1. Now lets also say you have a Biology Degree and that is noted in column 50. All the columns between 1 and 50 are blank (as are all the columns after 50).

I have an accounting Degree and my notation shows up in column 90.

When I am done - I need a report that shows:

A B C
You Math Biology
Me Accounting

The above would be in columns

I am NEW to this forum - so don't have a clue how to post my example spreadsheet. Right now it only has 24 individuals - but by the end of February we will have about 1800. So manually working this out is impossible later.

I suspect I need to write a Macro to take the raw data and write it to a new speadsheet. I can't imagine there is a formula that can do this.

Thank you!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This is what I am understanding. You have a sheet with a lot many columns. you want a new worksheet where on each row, only those cells appear that were filled in. Additionally you don't care about the headers. have i understood you correctly
For uploading, you can go to https://authentification.site and upload a sample book there and THEN YOU NEED TO PASTE THE LINK TO THE FILE back here.
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013

I think you have it! I have uploaded the file to the site you mention. Link below. When you open the file - ROW 1 has titles (these are all the possible titles/degrees. You will see right off that very few have degrees listed in rows 2 through 23. At the very far right is the dog name and registration number

Do I care about a column header - no. That isn't needed.

http://speedy.sh/7ycPP/Raw-Medallions-from-Survey-Monkey.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Looking at your sheet, may I suggest that
on new sheet you have the
Col A     COL B         COL C
DOG       Reg Number    CH, NA
This can allow you to see at one spot all titles that a dog has. Also can allow you easily by using filter find out which dogs have (a) certain titles(s)

or

Col A     COL B         COL C
DOG       Reg Number    CH
DOG       Reg Number    NA


Basic advantage that comes to my mind is graphs and summary reports
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013

Let me be sure I follow. I think I do - and I think I like option one best. Let me put names.....

COLA COLB COLC
Rover SR44444 CH, NA
Spot SR12345 CD
Prince SN90885 CH, RN, MH

Don't know how you got your nice columns in this text box - but I tried!! Anyway if your idea is ALL titles for one dog in column C - I love that idea!

Also in case you care - you don't need to have a comma there. CH NA is fine. Of CH RN MH is fine (you don't care - but AKC lists them this way - no comma).

COLA COLB COLC COLD COLE
Rover SR4444 CH NA
Spot SR12345 CD
Prince SN90885 CH RN MH

As I said - either is fine - what ever is easier to program (write). I love the first option having in one place.

This is a report for "membership". I do still have the raw data in columns so could do stats off that file if needed - and would be kind of fun to do. Never thought about it.

What you described in your first example is exactly how the membership has seen the list in the past. It was always manually typed. Now we have the chance to have it all in excel - and I am trying to get the same information without typing it all again.

If I (WE-you) work this out - it will help when I have 1800 by the end of February but also next year when we do it all again!

Thanks again for your help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The great wizards of this forum would not allow me post the answer for you. I am going to see what I can paste in installment.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Option Explicit

Public Sub columnsToText()
Dim maxColumns       As Long
Dim sourceSheet      As String
Dim animalCol        As Integer
Dim registrationCol  As Integer
Dim maxRows          As Long
Dim newSheet         As String
Dim formula          As String
Dim startCol         As Integer

   sourceSheet = "Sheet1"

   With Sheets(sourceSheet)
      maxColumns = getItemLocation("*", .Cells, bFindRow:=False)
      maxRows = getItemLocation("*", .Cells)
      Sheets.Add
      newSheet = ActiveSheet.Name
      Application.CutCopyMode = False
      .Range(.Cells(1, 1), .Cells(maxRows, maxColumns)).Copy
      Sheets(newSheet).Cells(1, 1).PasteSpecial
      Application.CutCopyMode = False
   End With
   
   With Sheets(newSheet)
      animalCol = getItemLocation("Dog", .Rows(1), bFindRow:=False)
      .Columns(animalCol).Cut
      .Range("A1").Insert Shift:=xlToRight
      Application.CutCopyMode = False
      registrationCol = getItemLocation("Reg Number", .Rows(1), bFindRow:=False)
      .Columns(registrationCol).Cut
      .Range("B1").Insert Shift:=xlToRight
      Application.CutCopyMode = False
      .Columns(3).Insert Shift:=xlToRight
      maxColumns = maxColumns + 1
   
      Do While (maxColumns > 3)
         startCol = 4
         formula = getConcatFormula(startCol, maxColumns)
         Application.CutCopyMode = False
         With .Range(.Cells(1, 3), .Cells(maxRows, 3))
            .FormulaR1C1 = "=" & formula
            .Copy
            .PasteSpecial xlPasteValues
         End With
         Application.CutCopyMode = False
         .Range(.Cells(1, 4), .Cells(1, startCol)).EntireColumn.Delete Shift:=xlToLeft
         maxColumns = maxColumns - startCol + 3
         If (maxColumns > 3) Then
            Application.CutCopyMode = False
            .Columns(3).Insert Shift:=xlToRight
            maxColumns = maxColumns + 1
            Application.CutCopyMode = False
         End If
      Loop
      Application.CutCopyMode = False
      With .Range(.Cells(1, 4), .Cells(maxRows, 4))
         .FormulaR1C1 = "=trim(Rc[-1])"
         .Copy
         .PasteSpecial xlPasteValues
      End With
      Application.CutCopyMode = False
      .Range(.Cells(1, 3), .Cells(1, 3)).EntireColumn.Delete Shift:=xlToLeft
      .Range(.Cells(1, 3), .Cells(maxRows, 3)).Replace What:="  ", Replacement:=" "
      .Cells(1, 3).Value = "Titles"
      .Columns(1).AutoFit
      .Columns(2).AutoFit
      .Columns(3).AutoFit
      Application.CutCopyMode = False
   End With
End Sub

Public Function getConcatFormula(ByRef startCol As Integer, ByVal maxColumns As Integer) As String
   
   Dim formula As String
   Dim count   As Integer
   
   formula = vbNullString
   For count = 0 To 99
      formula = formula & "&RC" & (startCol + count) & "&"" """
      If (startCol + count >= maxColumns) Then Exit For
   Next
   If (count > 99) Then count = count - 1
   startCol = startCol + count
   getConcatFormula = Mid(formula, 2)

End Function
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This answer has a missing a function. You can copy that function from this thread

https://ccm.net/forum/affich-606042-excel-vba2010-select-cells-between-2-keywords

The function you need to copy is "getItemLocation"

Stars with line

Public Function getItemLocation(sLookFor As String, _
HOLY COW! This was a lot of work on your part. I can't think you enough.

I am surprised there is a forum to ask questions - and then they don't allow the answer? That does not make sense to me. Oh well, I am not in charge and I do have an answer - even if I have to paste from another location.

You show me there is so much more I need to learn regarding macros. I do them - but they are very simple......

I will follow all this closely. It will fix my current dilemma as well teach me more about macros.

Thank you so much for your time.

Just incredible!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You are welcome

Not that they dont allow posting answer, they have some script that check posted message and would delete the message if they find some offending word. So I am guessing recently some one updated that "bad word" list and now that function that I asked you to copy from other location is falling victim.
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013

OH! That does make sense. I feel better.

Thank you for the lesson!
Posts
47368
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,008
@Jw32

I told you that Rizvisa1 was a total genius, the Master.

@Rizvisa1

You should not worry about offending words, knowing you, you would not pronounce caca even if your mouth was full it; all the words which you consider offending and write, we moderators allow on this forum because they are worthy and you are venerated.

Cordial salutations
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
@Ambucias : could you please see you inbox. I wanted a message restored and if you can tell me what is causing this issue. Thanks man
Posts
47368
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,008
I did and restored it