Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find Multiple Instances Of Single Criterion In Row & Return To A Single Col
Find Multiple instances of Numeric Criterion in Row & Return To a Single Column. I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows. Each Row may contain duplicates of the Numeric Criterion. I would like to find ALL instances of a specific Numeric Criterion across each single Row in the Dynamic Range "Data" and have the Results returned to a New Sheet in a single column. NEW Sheet: The Numeric Criterion is housed in G5. The matched criterion should be returned to the New Sheet starting at G7. Duplicate instances in the same Row should ALL be returned to the same cell in Column G on the New Sheet. Sample Data Layout: Columns I J K L M N O P Q R Row No.76 1 0 1 1 0 1 1 1 0 1 Row No.77 2 2 3 2 1 2 2 0 0 0 Row No.78 3 3 3 3 3 0 3 0 3 0 Scenario: Looking for Numeric Criterion 1 (one). Expected Results - New Sheet: Row No.7 Column G (Cell G7) 1111111 Row No.8 Column G (Cell G8) 1 In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be returned to the same cell G7. In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it should be returned to cell G8.
View Replies!
View Related
Split Single Cell Into 2 Column X 3 Row Range
I'm trying to separate a cell containing: TSB w/6.5% NaCl - NS 29002TSB w/6.5% NaCl - NS 29003TSB w/6.5% NaCl - NS 29004 into a 2x3 table, in another sheet, like this: TSB w/ 6.5% NaCl NS 29002 TSB w/ 6.5% NaCl NS 29003 TSB w/ 6.5% NaCl NS 29004 TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items
View Replies!
View Related
Adding Multiple Row Contents Based On The Content Of A Single Cell
Been trying to work this one out and have ended up banging my head against the wall. Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability. The team name is a selection from a list, the availability is 1-6 (different levels of availability) I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up: Name Team 1st 2nd 3rd 4th 5th 6th 7th etc. Mr X ___1st___1______2__1 Mr Y ___2nd_______2________5__5__5 Mr Z ___1st___1____________3__3__3 Total 1st: 2 2 1 3 3 3 Total 2nd: 2 5 5 5 So basically the formula in the last row goes along the lines of: Add each cell in the column IF the team name = 1st
View Replies!
View Related
Format Row Based On Single Cell Value In That Row
I have a macro that's supposed to see if cell 5 meets criteria and continue to all sheets in workbook, except " Total". When I activate the macro on a specific page, it runs smoothly on that page. But when it's finished, it doesn't continue to the next sheet in the workbook. Sub ColourBG() Dim ws As Worksheet Dim line As Integer endline = Range("A1000").End(xlUp).Row For Each ws In Worksheets If ws.Name <> "Total" Then With ws For line = 3 To endline Application. ScreenUpdating = False If (Cells(line, 5).Value = "0206") Then _ Cells(line, 1).EntireRow.Font.ColorIndex = 5 '*(Blue) Next line Application.ScreenUpdating = True End With End If Next ws End Sub
View Replies!
View Related
Multiple Date Column To Single Column & Sort
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View Replies!
View Related
Single Row Into Multiple Rows
Is there a way convert single cell with wrap text into multiple cells with the number of resulting rows based on the number of lines in the original cell. Due to the restrictions of my overall printable "document", I must use multiple cells of the same height. Any change of row height would throw off the desired final result. As an example, when I start with my original cell in text wrap and set column width I get something like the following in cell F2: ASTM A743 UNS J93254 (SUPER AUSTENITIC) (LONG LEAD TIME MATERIAL) This looks great, but it is all in one cell, and the four lines of wrapped text have obviously increased the row height which is bad. I have tried using the LEFT and MID functions to force the text into different rows based on character length, but this is sort of messy since I am never sure what the input will be resulting in something like the following (this time in cells F2:F5) with spaces at the beginning of a line or breaks in the middle of words: ASTM A743 UNS J93254 (SUPER AUSTENITIC) (LONG LEAD TIME MATE RIAL) My ultimate goal would be to somehow format the original input data text so that regardless of the input, the text would be restricted to a certain column width, sending the remaining text to the next consecutive rows down, and maintain the integrity of the original. I can have as many intermediate cells, formatting as I go to get to this point. Of course, I could manually enter the text in the different rows every time, but it would be nice to have a succession of formatted cells so that the desired end result would be automatic. Hopefully, the answer does not involve too much in the way of macro or any VBA code since I am a pitiful novice, and we are sort of under pressure to get this project under way.
View Replies!
View Related
Consolidate Multiple Rows Of Data Into Single Row
I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation: I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts. Current: ID Name Address Program Amount 1 Bob home A 25 1 Bob home B 37 2 Dave home A 22 2 Dave home B 10 2 Dave home C 21 Need: ID Name Address Program A Program B Program C 1 Bob home 25 37 0 2 Dave home 22 10 21 Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.
View Replies!
View Related
How To Convert Multiple Coloums Data Into One Single Row
I have a data in different coloumns, that I want it to in one single Coloumn but different rows. The data is like below A B C D E F 1 Outer Inner Tip Nose 2 Outer Zip Stiffner Nose 3 Inner Tip 4 Zip Stiffner The data are in A to F coloumns & in Different rows. This I want in one row - Like Outer Inner Tip Nose Outer Zip Stiffner Nose Inner Tip Zip Stiffner
View Replies!
View Related
Organize Data From Multiple Rows Into Single Row
I would like to automate, due to how long it would take to manually organize the data myself. I have all of the raw data needed organized into 3 columns. The problem is that some of the related data is divided up into multiple rows, based on ID number. What I would like to do is take the related CPT codes and respective descriptions, from the same ID number, and place them on the same row. I am going to include some pics and even a small chunk of the source data. Here is a pic of the raw data: And here is the first 3 already done: The number of CPT codes per ID number range from 2 - 5 I believe, maybe 6. Not sure if this has any bearing on how it is done.
View Replies!
View Related
Data From Multiple Rows Into Single Column
I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column. It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...
View Replies!
View Related
Move Multiple Columns To Single Column
How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional. I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.
View Replies!
View Related
Copy Multiple Rows Data To Single Column
transposing and sorting data into multiple columns. Column 1 for example will have the Parent's Name. Column 2 will have the Children. If Mark has 3 children, X Y and Z Jim has two children, A and B then I want Column 1 to display Mark and Jims Name and column two should display all the children Here is how I want the data: Column1 Column 2 Mark X Mark Y Mark Z Jim A Jim B Please see attachment.
View Replies!
View Related
Move Data From Single Column To Multiple Columns
I saw this thread from June Cut & Paste Macro: Move Address From Column To Row and I think this what I would like to have my macro do. I have one column which is copied and pasted as text in excel and there is several blank rows. I attached the spreadsheet example - it has 40 lenders, with the top row being the lender name (A1), followed by address(A2), city-state (A3), two blank rows(A4-A5), lender type (A6), approved date (A7), one blank row (A8), lender specialty (A9), telephone (A10) , e-mail address(A11) and two blanks rows (A12-A13) and A14 starts over again. For this example there should be 8 columns and 40 rows (lender name, address, etc...). As I mentioned I think the previous thread's macro would work with some minor tweaks. I couldn't figure out the best way to handle the blank rows and or remove the blanks and what to add or take out of the macro code.
View Replies!
View Related
Split Single Column Into Multiple Columns And Rows
I have 300 rows worth of data that looks similar to this, all organized in one column: John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Snider Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555 As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this. John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555 Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
View Replies!
View Related
Format A Single List Into Multiple Lists
I have a single list of data that I am trying to convert into multiple lists. Here is a sample of what I have: COL-A COL-B 1 Animals 2 Dogs 3 Bulldogs 4 Missy 4 Rex 4 Fred 3 Terriers............... Hard to describe, but the 1 should have the 2's under it. Each 2 should have the 3's. Each 3 should have the 4's, etc... I am not sure how to put tables into this so I will try to draw a few. COL-A Animals Dogs Cats COL-B Dogs Bulldogs Terriers Dalmations.................. The order of the columns do not matter. If anyone has any ideas how I could do this that would be great. The data is on seperate sheets. I would love to be able to do it with INDEX and MATCH criteria, but a VBA solution would be acceptable.
View Replies!
View Related
Sort Info In Column Into 50 Row Blocks
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here... 2 2 2 2 33 33 33 4 4 4 4 4 4 etc. Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no. so that it looks like this 2 2 2 2 46empty rows here.......................
View Replies!
View Related
Coply Multiple Lines Into Single Cell
I have a table in word that I'm trying to copy into excel. The problem is one of the columns in the row in that table may have multiple lines with hard returns. When I copy and paste into excel, the result gives me multiple lines. I want a single line, with the exact multiple text in a single cell. How can I do this?? Nobody in the world seems to know. To try this set up a word table with a single row, 3 columns. In the 3rd column put the following value. Value 1 Value 2 Value 3 Now copy the entire row and attempt to past into excel. Watch as it creates 3 rows.
View Replies!
View Related
Indentify Value From Multiple Values In A Single Cell
I am looking to produce a formula that shows if a number is "found" or "not found". I have list of over 1000 numbers in one column (1 number per row) . I am trying to identify if those values are present in a range of numbers from another worksheet(also in a single column). My challenge is that the range of numbers that I am looking in has multiple values, separated by commas in a single cell. Looks like it was from an Access data dump.
View Replies!
View Related
Multiple Numerical Formats In A Single Cell?
In a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
View Replies!
View Related
Sorting Multiple Customers In A Single Cell
I am trying to figure out how I can sort multiple customers in a single cell, and then assign the customers to the outside salesman. The basic idea is in column A I have the customers for a specific job. Assume I have three customers (Alpha, Omega & Gamma) and they are all in ONE cell. I need to sort this and assign each customer to each of my outside salesmen (Bob, Ted, Fred) in another column. The other component to this question is that Bob, Ted & Fred may have MORE than one customer in that single cell........
View Replies!
View Related
Multiple Rounding Conventions Per Row/column
I am working on a 1000+ cell spreadsheet that contains ~ 20 different formulas in randomly distributed cells. The the final values range from approximately 3,000,000 to 0.000000005. I need to round the products of these formulas to the nearest whole number or the first significant digit to the right of the decimal. Is there a way that this can be done without editing every cell? I am trying not to disturb the cells as that is where mistakes were made in the past.
View Replies!
View Related
VLookup Work With Multiple Numbers Within A Single Cell
I built an Excel Macro to pull information from my schedule and compile it within another Worksheet. The Macro below searches for an instance of "Unavailable" and then populates my Data Worksheet with the WeekDays (ie: M-W-F or ALL Week), the Details (ie: Vacation or Training) and the Name of the employee. I struggled with a way to populate my employee name as "unavailable" isn't always offset by the same amount of characters to employee name (as it is with WeekDays and Details). Instead, I collected the row in which "Unavailable" was found. I used the Vlookup function to populate the number of Absences and it worked swell. =VLOOKUP(C2,O:P,2,FALSE) C2 is populated with Weekdays (ie: ALL week or M-W-F) O contains a list of possible day combinations P contains the number of days I hoped to use a similar function to populate my employee names. This didn't work well a 'tall. =VLOOKUP(A2,L:M,2,FALSE)....................
View Replies!
View Related
Multiple Duplicate Lookup Results In Single Cell
I have attached a copy of what I am trying to do. I've been researching vlookup for a while and everything I try doesn't seem to work. I'm also fairly new to Excel, so most of this is my first time trying these formulas. In the attached test.xls file, I have two sheets created. The first is "Responsibility," and the second is "List." The data in "List" is what I am trying to pull from. As you can see, the people's names are listed more than once as the list goes down. On the "Responsibility" page I have each person's name one time. In the "Extinguisher" column, I'm wanting it to list every number that is found next to the person's name on the "List" sheet. For example: Column B2 on the "Responsibility" sheet should read as follows: 1,2,3,17,24 Here is the formula I have in these cells: =VLOOKUP($A2,List!$A$3:$C$38,3,TRUE)
View Replies!
View Related
Referencing Multiple Cells From Inside A Single Cell
I have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase. Example: ="Jackpot: &G2 (&G1) / &G3 = &G4 each" I already tried this: ="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each" Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.
View Replies!
View Related
Reference Column To A Single Cell
I'd like to reference Column A to a single cell in a different sheet. =StoresServiced!A2 Returns the value of A2 in the sheet called StoresServiced to another sheet. What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort
View Replies!
View Related
Find & Copy Multiple Results Into Single Cell
I have a macro which scans column "AV" in Sheet1 and if a particular value is found it will copy certain cells from that row and paste them into Sheet2 on Row 10 starting with column "E". With the current code I only paste a single instance, if the value is found more than once I only have the last one pasted. How can I best set this up so it will paste ALL found value results within Row 10? The four copied results do not have to be in separate cells, it is actually best if the cells are combined. On Sheet2, cells E10-H10 can also be setup to have the data pasted across all four cells and that way Row Height AutoFit would take care of expanding the row's height to accommodate the possibly of having four or five sets of results being combined together and pasted into E10. Rather than the typical result appearing as: [TABLE]______E___F___G___H 10__1223 122 2222 2222 11__343 565 4554 5990 12__9755 334 6787 6788[/TABLE] It is better for me if the data appears like this: [TABLE]_____E____F____G____H ____1223 122 2222 2222, 343 10__565 4554 5990, 9755 334 ____6787 6788 [/TABLE] Is this even possible using a macro? It is currently being done using a Word document but I am wanting to automate the process and try to handle all the data sorting within Excel, if possible. Sub Number() Dim colSearch As Range Dim celVal As Range Dim celRow As Range With Sheets("Sheet1")
View Replies!
View Related
Concatenate Multiple Cells Into Single Cell With Line Feeds
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary. would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
View Replies!
View Related
Speed-Up Multiple Row/Column Deletion Macro Code
I have the following function in a worksheet module: Private Function NixEmptyColumnsAndSuperfluousRows() Me. Range("A1:A7").EntireRow.ClearContents Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp Me.Range("BE1").EntireColumn.ClearContents Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft Me.Range("AT1").EntireColumn.ClearContents Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft Me.Range("AK1").EntireColumn.ClearContents Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft Me.Range("Y1").EntireColumn.ClearContents Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft Me.Range("O1").EntireColumn.ClearContents Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft Me.Range("K1").EntireColumn.ClearContents Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
View Replies!
View Related
Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt: A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ... What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with A1: A-dec International Inc. A2: A. Bellotti A3: A. DEPPELER S.A.
View Replies!
View Related
Combine Multiple Records Onto 1 Row Based On Unique Number In Column
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel. I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column. What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example: BEFORE HH Acct 1 1234 1 2345 1 3456 1 4567 2 9876 2 8765 3 1113 4 5556 4 4447 4 3335 AFTER HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7 1 1234 2345 3456 4567 2 9876 8765 8765 3 1113 4 5556 4447 3335
View Replies!
View Related
Transpose Column Range Into Single Cell With Delimiter
I want to add a Punctation mark (comma), like this: , and also want to add punctation mark (colon), like this: : In this moment I have below macro: Public Sub CombineCells Dim Combined As String Combined = "" For Each Cell In Selection Combined = Combined & Cell.Value & ":" Next Cell Selection.Cells(1, 4).Value = Combined End Sub the effect shoud be like this: before: --A 1-C 2-D 3-E 4-F Etc. after transposed: --D 1-C:D,E:F Etc.
View Replies!
View Related
Add Single Character Before & After Each Cell Value In Column
I am trying to do is put an asterisk at the beginning and the end of the Information in a cell for the entire column. Is there a way to make a macro that, once I highlight a column, it will put an asterisk at the beginning and end of the information in each cell in that column until a blank cell is reached where the macro will then stop? This will save me a lot of work. It will work for me if a particular column has to be defined in the macro, but would be better if it is one I can just highlight. This would also work fine for me if I just had to highlight the cells that I want to use as well. Sorry if this seems to be a stupid question, but I am a very novice excel user
View Replies!
View Related
Copy Range To Row Below Last Used Cell In Column & Format
I'm trying to Copy and Insert formulated cells from sheet "Add Entry" range A3:AZ4 to Sheet "January" just above the last row containing data (the Totals line) starting in column B. The cells need to remain the same in cell size and maintain the formulas that are inmbedded within them. I want to be able to do this repeatedly by applying this macro to a button. I would also like (if possible) to alternate the color of these cells in an alternating row fashion.
View Replies!
View Related
|
TRACKER
