Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
ADVERTISEMENT
May 6, 2014
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
View 5 Replies
View Related
May 6, 2014
I have data in columns E through J under the headings of Monday-Saturday (E is Monday, F is Tuesday...J is Saturday). Then out to the left of the data, in column A, I have the week ending date (using Sunday as the last day of the week) which corresponds to the data under each day. There are multiple rows with different week endings but all the data is under columns E-J with the week endings in column A. So for instance, on row 13 the week ending in column A is 12/22/2013 (a Sunday) which means the corresponding data in the same row under columns E-J (the Mon-Sat columns) belong to 12/16/2013 (the Monday in the week ending on 12/22/2013), 12/17/2013 (the Tuesday in that week), 12/18/2013...and so on and so forth until 12/21/2013 which is Saturday in the week ending of 12/22/2013. Lastly, out to the right starting in column N and going horizontally out to column FD (will go out further as more days are added) I have the specific dates by day, it starts with 12/16/2013 and goes on incriminating by 1 day at a time until the end of all the dates included in the data (which at this point so happens to be 5/11/2014).
What I need done now is to take all the data which currently sits in columns E-J (the Monday-Saturday columns) and copy and paste it out under the correct specific date column that it belongs to starting in column N (going all the way out to FD) based on the week ending in column A. So with row 13 since the week ending in column A is 12/22/2013 the data in columns E-J needs to get pasted to row 13 columns N-S, then the same thing for row 14, except the week ending in column A row 14 is 1/5/2014 so the data in columns E-J row 14 needs to get pasted to columns AB-AG (AB is 12/30/2013...and AG is 1/4/2014). Below is the loop I came up with that accomplishes this, but for the reasons already stated.
Code:
Sub newtest()
Sheet2.Unprotect
Dim drd As Long
Dim rrd As Long
drd = 13
rrd = 14
Do Until IsEmpty(Cells(drd, 1).Value)
[Code] ..........
View 8 Replies
View Related
Aug 24, 2012
I am trying to convert
Excel 2010
A
B
C
D
[Code].....
View 9 Replies
View Related
May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
View 9 Replies
View Related
Mar 27, 2014
Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)
I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).
I need to put the items into a form, one order per ETID.
I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.
I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.
View 1 Replies
View Related
Jul 6, 2014
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
View 6 Replies
View Related
Apr 25, 2014
I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
View 9 Replies
View Related
Jan 15, 2013
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
View 12 Replies
View Related
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies
View Related
Jul 11, 2012
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
View 8 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Jul 31, 2014
When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.
If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.
View 5 Replies
View Related
Sep 19, 2013
In column A, I need to highlight the cells green if the value is less than < the values in columns B and C, yellow if A = either B and C, and red if A is greater than B and C. But I only want the formatting to stay in column A. I'm using Excel 2010 and Windows 7.
A
B
C
Green->
444
512
482
Yellow->
364
571
364
Red->
101
99
87
View 2 Replies
View Related
Nov 21, 2011
My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.
I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.
PS.Using Excel/Outlook 2010
View 1 Replies
View Related
Jun 4, 2013
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
View 1 Replies
View Related
Dec 9, 2011
I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.
More Specifically:
| Part # | Description | Category | SubCategory | Cost | Labor Hours | Weight | Etc |
I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.
Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)
What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.
Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).
The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...
Can I make it so that it will insert Pre-Specified information into both sheets at the same time?
View 1 Replies
View Related
Apr 15, 2014
I am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
View 5 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
Jul 3, 2013
I was planning to transpose this sample data into the output designated below.
Original sample: (There is an empty row after every 4 entries)
Name: xxx
Email: xxx
Phone Number: xxx
Address: xxx
[Code].....
View 9 Replies
View Related
Mar 7, 2012
I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.
Each contact entry takes the follwing form(comma's denote new columns)
55555, Name , John, Smith, Tel: , 555-555-5555
ID , Company, XXX , , Email, xxxxxx
Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.
I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.
View 1 Replies
View Related
Jul 6, 2014
I have a situation where I have source data formatted like this:
Document ID
Document Name
Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
Document ID
Document Name
Author
[Code]....
View 3 Replies
View Related
May 4, 2014
I have column A and it has 1000 rows, every row has a number in it, from 5000 to 5200, meaning that some numbers are presented multiple times in column A.
I need to lose repetitions, so every number is in the the table only one time and then I need to convert this one long column into, for example, 9 columns, so there's no wasting of space and have only one column in every page, if printed out.
View 5 Replies
View Related
Jul 18, 2007
I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?
View 9 Replies
View Related
Jun 23, 2006
It lists single clients on single lines, with various column fields that have autofilters (such as where th client came from, when they arrived, who is dealing with them, are they complete and so forth).
The final entry I need to include is a list of th policies (it is a financial business) they need us to consider, split into three columns of life policies, pensions and investments.
My problem is that I am unable to put more than one policy in each of those columns which can then be sorted through the filters.
How am I going to achieve this, yet still keep one client per line?
View 6 Replies
View Related
Nov 1, 2006
I'm making a database for people to easily find their documents at work. In some cells there are multiple entries because the document could be within two catagories, eg. memo and report. Is it possible to somehow list these multiple entries within the cell so that the autofilter will recognise them as seperate entries and find that document whether you filter for memo or report? If not, can you get the filter to search more than one column for the same result and show all entries that are, say, a memo, even if it says so in different columns? I want it to be as simple as possible for the user so that they can select what they want from the drop-down menu and not
View 9 Replies
View Related
Jul 10, 2014
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
View 4 Replies
View Related
Mar 4, 2013
if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.
View 2 Replies
View Related
