Many Columns return into fewer columns.
Solved/Closed
JW32
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
-
Jan 4, 2013 at 04:46 PM
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - Jan 8, 2013 at 04:31 PM
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - Jan 8, 2013 at 04:31 PM
Related:
- Many Columns return into fewer columns.
- Display two columns in data validation list but return only one - Guide
- Tweetdeck larger columns - Guide
- Zuma return - Download - Puzzle
- How to return on flipkart - Guide
- If cell contains date then return value ✓ - Excel Forum
7 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 4, 2013 at 06:50 PM
Jan 4, 2013 at 06:50 PM
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
JW32
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
Jan 4, 2013 at 08:04 PM
Jan 4, 2013 at 08:04 PM
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!
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!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 5, 2013 at 07:49 AM
Jan 5, 2013 at 07:49 AM
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.
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.
JW32
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
Jan 5, 2013 at 04:02 PM
Jan 5, 2013 at 04:02 PM
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
Do I care about a column header - no. That isn't needed.
http://speedy.sh/7ycPP/Raw-Medallions-from-Survey-Monkey.xls
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 5, 2013 at 04:42 PM
Jan 5, 2013 at 04:42 PM
Looking at your sheet, may I suggest that
on new sheet you have the
Basic advantage that comes to my mind is graphs and summary reports
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
JW32
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
Jan 5, 2013 at 04:56 PM
Jan 5, 2013 at 04:56 PM
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.
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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 6, 2013 at 08:51 PM
Jan 6, 2013 at 08:51 PM
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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 6, 2013 at 08:51 PM
Jan 6, 2013 at 08:51 PM
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 6, 2013 at 08:54 PM
Jan 6, 2013 at 08:54 PM
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, _
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, _
Didn't find the answer you are looking for?
Ask a question
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!
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!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 7, 2013 at 10:01 AM
Jan 7, 2013 at 10:01 AM
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.
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.
JW32
Posts
8
Registration date
Friday January 4, 2013
Status
Member
Last seen
February 21, 2013
Jan 7, 2013 at 10:49 AM
Jan 7, 2013 at 10:49 AM
OH! That does make sense. I feel better.
Thank you for the lesson!
Thank you for the lesson!
Ambucias
Posts
47310
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
February 15, 2023
11,164
Jan 7, 2013 at 05:36 PM
Jan 7, 2013 at 05:36 PM
@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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 8, 2013 at 01:45 PM
Jan 8, 2013 at 01:45 PM
@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
Ambucias
Posts
47310
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
February 15, 2023
11,164
Jan 8, 2013 at 04:31 PM
Jan 8, 2013 at 04:31 PM
I did and restored it