Separating From Multiple Rows Into Columns
Mar 25, 2014
I have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).
I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.
I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...
So every place where there is a value in col F I'd like the list of secondary issues in the same row.
View 4 Replies
ADVERTISEMENT
Apr 8, 2013
I have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)
For example: "1100789/3200899/6xlm-sgt-455-0987"
The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)
I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.
for example:
1 100789
3 200899
6 xlm-sgt-455-0987
Is this possible with the data tools in a spreadsheet, or will I need to write a macro?
View 9 Replies
View Related
Nov 14, 2009
I need to separate multiple values separated by comma in a single cell into individual rows. However, I also need that the data in the adjacent columns of the original cell also to be repeated in the new rows. The problem is illustrated in the attached file.
that the table is just a sample and most of the tables I work with contain at least 15,000 rows,
View 7 Replies
View Related
Dec 18, 2007
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
View 4 Replies
View Related
Jan 15, 2010
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies
View Related
Sep 21, 2013
I have a column with random times that are not seperated by any commas or spaces how can i seperate them into different columns. For example in A1 i have 12:3213:5420:32 this represent three different times 12.32 13.54 and 20.32 (each time has the same format as shown) and i want to display these as B1=12:32 C1=13:54 and D1=20:32 then do the same for the rest of column A?
View 3 Replies
View Related
Dec 2, 2008
Seperating TEXT in different columns placed at non-fixed location in a cell...
I have some data which contains people's name and the places ( i.e Cities) mixed which I need to seperate, however the challenging part there is no consistency in data as the city can appear first in the middle , last or even just the city name in the cell but it needs to be seperated ...
In Column B I have the following type of DATA ...
View 11 Replies
View Related
Jun 24, 2014
I want to separate the text into columns as in Table
Name
Name
Contract
Ref. No
[Code].....
View 5 Replies
View Related
Oct 2, 2008
I have a list of 2500 companies. In each cell there is the company name, street address, and phone number. And of course, each one is of varying length. I need a way to put the company name, street address, and phone numbers in seperate columns. I tried going to text to columns but it wouldn't work due to the varying lengths.
View 5 Replies
View Related
Jan 27, 2014
I have to separate text & numbers in different columns.
e.g. 349,910 American Capital Ltd. (a) 4,902,239
i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle.
View 11 Replies
View Related
Mar 13, 2014
As enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,
Total Amount = Amount X Exchange Rate
Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.
View 4 Replies
View Related
Apr 10, 2013
I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.
DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.
D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.
E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)
View 4 Replies
View Related
Feb 10, 2014
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head
75698 Dog & Duck
634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
View 5 Replies
View Related
May 13, 2013
I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.
ie. this is my data in column 1
1
2
3
4
5
6
7
8
would like to separate it into columns 2 and 3 as follows
1
.....2
3
.....4
5
.....6
7
.....8
I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.
View 4 Replies
View Related
Jan 9, 2014
I have an excel sheet that I'm trying to format. Basically, the excel book contains sheets (with the months), and each sheet contains a list of clients, their package, their status, date assigned, and date updated.
I've made another sheet (Sheet2) that fetches everything and consolidating it to one sheet. Sheet 2 also cleans up the data to display only the items that are 'completed'.
The issue I'm having now is in splitting the list (assuming it has been cleaned up to show only the 'completed' items. I would need to split the list first by the date they were updated, and then if they were assigned the same date or not. In short:
--Get all items with date updated = Month1
----If date assigned = Month 1: List items under table 1
----If date assigned < Month 1: List items under table 2
I've used macro on the excel (and I'm not sure it's efficient enough. I'm not proficient with VB, so...). I'm not sure if it will work on Mac.
View 14 Replies
View Related
May 18, 2009
I need to separate out some rows by date into new worksheets, but I need the dates to be a range. Our cycles are separated into periods, which last about 1 month, so I need one new worksheet for each period. I was thinking about having like a cover sheet that defines the exact dates (like column A has the period title "P1, P2, P3,..." and column B has the start date and column C has the end date).
Then on the next sheet is where I would keep my Input data. So I need to separate the rows of the input data sheet out by the periods. The date for each data entry is kept in column M. I have attached a sample workbook explaining what I tried to describe above.
View 6 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
Mar 3, 2013
I attached sample file with data I received (more than 50k rows in each file) is contained in 86 files with varying population lengths in each file that needs to be broken down as shown in the attached file.
View 5 Replies
View Related
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
Oct 10, 2008
I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.
At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.
It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
View 2 Replies
View Related
Jul 10, 2013
create a formula,for multiple criteria with multiple results in rows.
[URL]
View 1 Replies
View Related
Jun 19, 2009
I have a sheet that is organised as follows:
a) Column A contains 50 names, all unique. R1C1 heading is "Name".
b) Column B to Column H are for some text that can contain special characters, numbers, spaces etc.
c) R1C2 heading: "Day 1"
d) R1C3 heading: "Day 2". and so on till R1C8 having heading as "Day 7"
e) All the cells are filled in the specified range as specified in (b) above.
In summary, each of the names will have 7 different tasks associated with it in the row. These tasks can repeat for all the names.
For example: Consider the below set of data for 3 rows and 7 columns (spaces are used as column delimiter. In reality, spaces can form part of the tasks for each name):
Name D1 D2 D3 D4 D5 D6 D7
-------------------------------------
aaaa T1 T1 T1 T2 T3 T4 T5
bbbb T1 T2 T3 T3 T3 T4 T5
cccc T1 T4 T4 T4 T4 T4 T4
I hope its clear till this point.
Now my requirements are as follows:
a) Find out all unique elements from the set of tasks across all days and list them from 100th row onwards with each unique entry in new row.
b) For each of the unique tasks, list the names that have worked on that task in the same row.
For example, the output should be as follows:
T1 | aaaa, bbbb, cccc
T2 | aaaa, bbbb
T3 | aaaa, bbbb
T4 | aaaa, bbbb, cccc
T5 | aaaa, bbbb
The character "|" above is used as a seperator for columns.
View 5 Replies
View Related
Oct 21, 2013
Date
Name
Diesl
Petrol
Oil
Kerosene
[Code]...
Here from the above table i want to get the complete sum of all numbers corresponding to a specific date and person..
I used sumifs which looks like this: =SUMIFS(C2:F10,B1:B10,A12,A1:A10,B12)
But that doesn't work, should i resort to using multiple sumifs?
View 1 Replies
View Related
Nov 20, 2013
I have following information. I need to transpose the columns into rows
C
10001
V
1000
V
1001
V
1002
C
10002
V
1001
V
1003
C
10003
V
1001
V
1003
V
1004
The expected result should be like
10001 1000 1001 1002
10002 1001 1003
10003 1001 1003 1004
View 4 Replies
View Related
Mar 9, 2014
I'm trying to move multiple ROWS to COLUMNS. My data looks like this:
ID CLASS_NBR1 UNITS_TAKENGRADECLASS_NBR2UNITS_TAKENGRADE CLASS_NBR3
000555 688 1 A
000555 567 3 B
000555 888 1 C
000555 999 1 B
000555 889 3 C
101999 889 3 A
101999 567 1 B
I have between 4-9 rows that I'm trying to move to columns. I'm attempting to move data for each student on one row, currently each student has between 4-9 rows.
I've tried so many different formulas and MACROS that didn't work I started doing this one by one (manually) but I have too many rows and it's taking too long.
View 14 Replies
View Related
Mar 6, 2014
I am using the following range to copy data from Column A (from rows 3 on the the last row). How can I include Columns B and C into this range also, but still using Column A to find the last row?
[Code] .....
View 9 Replies
View Related
Apr 20, 2007
I have a file that I want Column D to average by column A and C. I also want to ignore any rows that dont have data in column B in the average. See sample file.
View 5 Replies
View Related
Jan 14, 2010
I have a spreadsheet that has ONE row for each day of the year and FOUR columns for each day. Some days use 1, 2, 3 or 4 columns. I need to turn these into rows for each Column.
Here is an example of the detail data: ....
View 9 Replies
View Related
Jan 17, 2014
I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
Test_Meal_Plan.xlsx
View 2 Replies
View Related
