How To Combine The Description In One Line
Jun 17, 2014Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.
View 4 Replies
ADVERTISEMENT
Combine 2 Cells With Line Breaks In Each Of Them And Keep Line Break Formatting?
Aug 5, 2014I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
Combine The Code Below In One Line?
Mar 24, 2009Need to combine the code below in one line?
View 2 Replies View RelatedCombine Area And Line Charts
Jan 14, 2010I have made two line charts from a selection of data and added "shapes" to fill certain areas under the lines. Is there a way of doing this within the chart rather than adding a shape over the top?
View 3 Replies View RelatedRow Combine Till Address Line Not Complete?
Jun 25, 2013I want to combine row while address row not complete. I attach example xls file in this post.
View 3 Replies View RelatedMany CSV Files Containing Common Names - Combine Across Sheets To One Line
Jun 27, 2013I work for schools use data to guide teachers practices in the classroom. My main function is to mine down through data for kids that teachers focus on specific skills with specific children.
I start with a big conference each summer. By hand I combine, by student name, the data collected over the past school year. We then tear down to student levels setting goals based on statistical analysis. I need to combine across all of these csv files in a way that appends the data from each into one line per kid. I thought that 'vlookup' or 'index, match' might do it. I don't know.
Combine 2 Rows Of Data Into One Line While Summing Their Contents?
Jul 1, 2013Basically, we have 2 tablets tallying data and writing to the same .csv file. That csv file is then copied into a front page of a much larger excel worksheet where the tallied data is sorted and used for a variety of reasons. This works perfectly using index and match when only 1 tablet is being used, however, when both tablets are writing to the same csv at the same time it creates 2 lines instead of 1 line.
For example 1 tablet might look like this:
Lot 45689 4 6 8 9 1 5
2 tablets looks like this:
Lot 45689 2 3 1 8 1 4
Lot 45689 2 3 7 1 0 1
for the tallies, i would imagine a =SUMIF function might suffice, but that doesn't solve my problem of there being 2 lines with the same lot #.
I am only dealing with this issue from the excel side, and have no control over the tablet functionality or the CSV file which further limits me.
Wanted to add that the Lot #s change daily and often so with my understanding of pivot tables this rules them out as a viable option.
How To Extract Qty Min/Max From Description
Jan 31, 2009Example: Table ...
View 9 Replies View RelatedLookup In A Description (color)
Mar 13, 2009I have a column with a description that includes color in the description, I want to sort by color with breaking up the description, is there a formula that will look at the description such as "Am Cannoe black tecan" and in the cel next to it come back with "black". Then I would be able to sort by color column, otherwise I would have to text to column and break up the description, etc,,, ect....
So column a1 has the description, I want a formula that will put in column b1 "black" I have 3 colors, so I can do one at a time or add to same formula.....
Sum Turnover Where Name Is PART Of The Description
May 14, 2009I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)
The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents. e.g. Paul is the description in am looking for in "Paul is riding his bike" If so, then add the turnover to Paul. I have made a demo to clarify what I mean.
Split Description Into 3 Columns
Dec 9, 2009I need help to split text (shown below) into 3 columns, now all text is in one cell.
e.g.,
HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286
I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.
Pull Down Option Description
Jul 29, 2009I have created a pull down menu but cannot figure out how to add descriptions with them. The items in the pull down menu are codes instaed of making people look up their definition in a printed out table,I am hoping to figure out how to make it so that as people scroll through the pull down menu a corresponding definition for the "code" listed appears.
View 9 Replies View RelatedFind First/Last Value In Row & Return Corresponding Description Above/Below
Apr 8, 2008I'm trying to find
1) the last and
2) first values in a time series of data in which the the data points are scattered all over the place
AND I'd also like to
3) get the corresponding year ( Heading name) for both the
3.1) last and the
3.2) first data point
I've found solutions for all but 3.2. Thus the question remains:
How could I lookup the heading description for the first value on a row?
example:
row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;
In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:
1) lookup last value in row
i) Find Last Number In Row & Return Heading
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution
ii) Find Last Number In Row & Return Heading
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below
iii) Find Last Number In Row & Return Heading
... 2 VB solutions
2) Lookup first value in row
Find Last Number In Row & Return Heading
=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))
... page also includes solution for 1), above
and
3) Lookup heading of last populated cell in row
Find Last Number In Row & Return Heading
=LOOKUP(2,1/(I9:CN9<>""),I$5:CN$5)
4) Could I e.g. modify the above to lookup the year of first value?
How To Display Description Of Options (list)
Feb 24, 2014Any way to display the description of options (list) which in this case is a numbering code (like ECG201) in a data validation cell.
The length of the cell is not long enough for the users to know the appropriate description of the selected lists. I am now thinking there should be a way to display the description of each item so that users will know that ECG201 means "International College of Excellence", for example, instead of having to go from one worksheet (area) to another to know what an option means.
Description Column In Pivot Table
Aug 10, 2009I often use pivot tables to summarize accounting data. I wish to summarize the data by account number, but also wish to display the account description next to each account number. Both the account number and account description are separate columns in my original table of data.
I've always managed to do this by the use of lookup formulas after the formation of the pivot table in a column outside the pivot table, but it would be preferable to have those descriptions as part of the table.
If I designate both the account number and account description as row labels, they land on two different lines.
Excel - Formula Map With The Description Of Another Sheet
Jul 3, 2014I'm trying to map the cases present in the sheet 1 to Sheet 2. Here the sheet2 I have highlighted the rows yellow color that needs to be updated by using excel formulas.Here the sheet should be updated with the description below mentioned along with the formulas..Highlighted cells in the sheet2 is B,C,I,J,T,U. I have designed the below condition in the same order
B cells should be updated with the reference of Sheet1 with the below condition:
Identify the "(B Value)"Claim with below condition (D Value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether (I Value) Mapped to the below Coverage in CAS (K value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether the Incident (Q value)is below for the Coverage (K Value)
J cells should be updated with the reference of Sheet1 with the below condition:
Verify the the Exposure type(P) is below for the Coverage (K)
T cells should be updated with the reference of Sheet1 with the below condition:
Verify the cost created the reserve coverage (K value) is below (N value)
U cells should be updated with the reference of Sheet1 with the below condition:
verify the line category of the payment done on the coverage
(X value of all the conditions for Sheet1 value)
User Defined Function Description
Nov 25, 2008I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.
View 6 Replies View RelatedInsert Columns Based On Tab Description
Apr 14, 2009I have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies View RelatedMultiple Sums Depending On A Description
Dec 28, 2005I really don't know how easy or difficult it might be, but here it is:
I have something like this (example) :
--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------
I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)
Excel Match Text In Description
Sep 2, 2013I have about 70K rows of data. This is a pull from a CRM system and one of the columns contains Free text which may or may not contain a reference ID.
So basically, I need to be able to identify which (if any) of the 60 or so Reference ID appears somewhere in the text. It may be at the beginning, somewhere in the middle or at the end.
So i created two formulas:
=ISNUMBER(SEARCH("MEBIG3",$P:$P))
I created 60 or so of these columns, all with a different Reference ID (MEBIG3 being the ID in this instance). I put the Reference ID in the Cell 1 of each of the columns. I then got a True in the cells wherever I matched the Ref ID to in the description field.
I then created another column with this:
=IF(AO4=TRUE,$AO$1,IF(AP4=TRUE,$AP$1,IF(AQ4=TRUE,$AQ$1,IF(AR4=TRUE,$AR$1,IF(AS4=TRUE,$AS$1,
IF(AT4=TRUE,$AT$1,IF(AU4=TRUE,$AU$1,IF($AV$2=TRUE,AV3,IF($AW$2=TRUE,$AW$1,IF(AX4=TRUE,$AX$1,
IF(AY4=TRUE,$AY$1,IF(AZ4=TRUE,$AZ$1,IF(BA4=TRUE,$BA$1,IF(BB4=TRUE,$BB$1,IF(BC4=TRUE,$BC$1,
IF(BD4=TRUE,$BD$1,IF(BE4=TRUE,$BE$1,IF(BF4=TRUE,$BF$1,IF(BG4=TRUE,$BG$1,IF(BH4=TRUE,$BH$1,
[Code] ........
So wherever there was a True, it returned the Cell at the top of the column which has the Ref ID in it. I put a pivot table and it worked, but the problem is that Excel just can't really handle the number of calculations. The file keeps crashing and becoming corrupt (There are 70K rows, times 60 or so columns). Also I have actually run out of columns I can add in the nesting, I really need more.
Any way of doing the whole piece of analysis all in one Cell. So basically it says "If ADM1 appears anywhere in Column P return ADM1, If ADM2 appears anywhere in Column P return ADM2 and so on".
Import CSV With Commas In Description Field?
Dec 2, 2013One of the fields I am importing has got a description with commas in. When I import the description is wrongly split. How can I fix that? KS14411011,22490030,"Airconditioner,Wall",5512
Code:
Private Sub cmdImportFile_Click()
Dim ImpRng As Range
Dim Filename As String
[Code].....
Looking Up Data And Match The Description And Then Insert The Value
Jan 21, 2008I have a table I down load from SAP with the following headers;
Route, Description, DChl, YF21 (SD),YF31 (SD),YF21 (MM),YF31 (MM),YF10 (MM),YF11 (MM),YF12 (MM).
All I need to do is look up and match the Description and then insert the value (they are percentages) found under the columes YF21 onwards.
I have tried VLOOKUP until I am blue in the face and cannot get it to work.
Insert Row & Description On Each Color Change
Aug 24, 2008I have a list of data in column A. This is grouped by a set of key information and then subsets. The beginning of the key data is signified by the cell colour blue and then the subsets signified by the colour yellow. I want to insert a row just below the colour blue and enter the value " Header" and then when each colour is yellow, insert a row and enter "Subset 1" for the first and then "Subset 2" for the second etc.. The number of subsets can vary.
ABC Customer (Colour Blue)
Info
Info
Product 1 (Yellow)
Info
Product 2 (Yellow)
Info
Info
Product 3 (Yellow)
Info
XYZ Customer (Colour Blue)
Info
Product 1 (Yellow)
Info
Info
Product 2 (Yellow)
Info
Should be:..................
Read Pdf Or Doc File Line By Line And Then Parse Data Into Proper Cells And Rows?
Feb 23, 2013I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedSearch Unopen Sheet For Value / Change Color Of Line / Copy Line Paste?
Nov 17, 2012Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
Compiling Data From Multiple Workbook Into A Line By Line Master Schedule
Mar 30, 2013I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
Read Range Of Data Line By Line Which Is Increasing Dynamically
Aug 20, 2014I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
How To Make Function That Works On One Line Work On Multiple Line
May 19, 2014I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
Macro To Assign Task Description To New Sheet?
Jan 8, 2013User inputs cost in a cell then clicks macro button that will run a macro that ask for a task description. The user inputs the task info that relates to the cost (i.e. weld pipe). This data is stored on a seperate sheet in the corresponding cell (cost is on sheet "hours" and task description is on sheet "task description" both in same cell. This allows a copy of the cost sheet to reflect the task description. I have tried recording macros but can't get anywhere.
View 1 Replies View RelatedDescription Outputs And Inputs Cells In Workbook
Feb 1, 2014I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.