to set up a rule so say certain numbers can be put into two groups in another cell.
i.e. codes 01,02,03,04 are grouped in country 1 and 05,06,07,08 are from country 2, so in the same row in a seperate cell it would identify if the product was 1 or 2?
I am looking for someone that might be able to help me with an unusual request. Here is the scenario:
I have an Excel table that pulls data straight from an Access DB. That Access DB pulls straight from a SharePoint list. When it is imported into Access, the records looks like this:
First NameLast NameIssueRemediationJohnSmithIssue 1
Issue 2
Issue 3Remediation 1
Remediation 2
Remediation 3
When it is imported into Excel, it keeps the formatting above (Multiple issues and remediation’s in 1 cell). I want to split those multiple inputs into multiple rows, without deleting the row below it. I would like it to look like this:
First NameLast NameIssueRemediationJohnSmithIssue 1Remediation 1 Issue 2Remediation 2 Issue 3Remediation 3
Or
First NameLast NameIssueRemediationJohnSmithIssue 1Remediation 1JohnSmithIssue 2Remediation 2JohnSmithIssue 3Remediation 3
how I can do this without manually going into the file and editing it myself every time?
i have export some data (contacts) from my mobile in excel in the following format
SURESH"""""""""""'',""""""""""9852653563""""""""""suresh@ril.com""""""""""""""
PRADEEP GOSWAMI""""""""",""""""""02836226358""""""""""""umamarine@yahoo.com
total 1000 contacts in this format now i want to split it them with suitable format
pls guide for it.
I tried for Text to column but the exported data is not in the sequence.
It has been awhile since last I have done this, but how do you split a data in the same cell. For example, I have "Smith, John" in the same cell, and I want to get "Smith" and "John" in two different cells.
View 6 Replies View RelatedI would have a preliminary data, a number, on sheet1. The final data would be on sheet2.
In the case where there is a whole number as the preliminary data, the data would remain the same as the final output. (eg. 248 --> 248)
In the case where there is a number with a decimal, I need a function where it looks at the original number as two sets of WHOLE numbers. (Eg. 248.30 --> 248 and 30). Where the digits to the left of the decimal remains the same and the digits to the right would be divided by x (in this case, 10). The final output would be 2483 (where 248 is the same and the 30 is divided by 10.
Since I am using an older version of excel, I cannot use quotient. I know that I would be using the vlookup function and perhaps an if function. however, I do not know how to separate the numbers in regards to the decimal.
What can split up text strings into two cells.
I have this in A1
"X,Y Coord Dn = ",38033.8,42701.7
I have this in A2
"Bearing = ",128.9
I want to split this text into 2 cells.
The Formula I am using for the B1 is:
=IFERROR(LEFT(A1,SEARCH(""",",A1)+1),"")
result = "X,
The Formula I am using for the C1 is:
=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1)))
result=38033.8,42701.7
The Formula I am using for the B2 is:
=IFERROR(LEFT(A1,SEARCH(""",",A2)+2),"")
result="Bearing = ",
The formula I am using for the C2 is:
=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1))) result=128.9
I am using the same formula down each column; But i can not seem to ge the "X,Y Coord Dn = ", to work properly.
I have a mixed string (i.e. containig character and numbers.... but the format is same PAR1 or PAMR1 or PR10 it can be 4 characters or five) i want a formula or a macro which splits it in two parts one containing characters and the other one containing nos.
View 9 Replies View RelatedI am trying to sum groups of cells where the groups contain a variable number of cells (within a column). My sheet has the following columns, C = plot#, G = plant#(within the plot), and F = number of tillers(for each plant). I need to get the total number of tillers per plot. Plots have different number of plants, so for instance cells c11:c16 are plot 3, cells g11:g16 are number 1 thru 6 (plant#), so I need the total number of tillers which would be sum(f11:f16). But each plot has a different number of plants. I've been told I'm not the best at explaining things,
View 2 Replies View RelatedI am having trouble figuring out how to split the data in a column.
For Example on the attached .xls - the first few records under the SIC column are:
73790200
59470104
70110100
581223
& how i want them to look in seperate columns:
73 790200
59 470104
70 110100
58 1223
I need the first two digits in their own column and the rest of the number in it's own column. I have over 6,000 records, so doing it manually is not a place I want to go!
im working with a number of related cells and am having trouble with entering formulas. at the moment im entering them in manually but im hoping theres a better way.
suppose I have a few groups of data. the first group is A1-C1, second group is D1-F1, etc. the values of these cells have to be output somewhere else. so if A1=dog, B1 = cat, C1=car then AA1 would be dog cat car. AB1 would be the concatenation of the next group.
Now my problem is how do i put a formula into AB1, AC1 etc. I cant drag and drop. If I drag AA1 to AB1, AB1 will be the concatenation of B1-D1 instead of D1-F1.
As an aside one thing I thought of is to just put the output cells further apart. So if the first output was to goto AA1 the next one could be output to AD1. The only reason I didnt do this is this sheet is wide enough anyway I didnt want to make it any wider. I also thought of outputting D1-F1 into AD1 and then copy/pasting those values into AB1 but that wouldnt work as I need these values to be changeable.
I am trying to divide the total of two groups of cells and I used the following formula:
=sum(B3:M3)/(B17:M17)
However, that is not a correct formula.
I cant seem to multiply two groups of cells at the same time. Its a hard one to explain so this is what I have tried to use and it doesn't work
=sum(a1:a5*5),(b1:b5*10)
so I tried...
=sum((a1:a5*5)(b1:b5*10)) and that didn't work either
what I want to do is add a1-a5 then * 5 and add it to the total of b1-b5*10.
I am working on this project for work that is pretty large (about 13500 rows) that is filled with items that I currently have sorted alphebetically. What I need to do is if there is a group of cells that have the same code, I need to insert a row at the very end of that group and autosum the quantity. It is becoming very time consuming doing it all by hand so I am trying to find out: is there a function that will automatically insert a row after each group of codes?
View 2 Replies View RelatedI have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:
=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.
So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.
I am trying to add 2 groups of cells and then divide the number by 60?
I can't get it to divide? It will only total.
=SUM(T807:T832)+SUM(T837:T846)/60
I have uploaded a sample sheet to illustrate what I am looking to do.
I want to pull out all instances where the four sheets of a 'book' are the same and appear in the same order. (column b is not really important) I have thousands of 'books' in the sheet so doing the task manually is not really an option.
I am trying to write a formula that will return a result based on which list a cell is found in.
Column A comprises of aisle/position information. The values range from B01 to B65, C01 to C65 ... H01 to H65 (Like aisles/shelf positions in a supermarket). This is not a static list, as the information in column A will change from week to week. It will be sorted prior to trying to work with it. The first three characters of the values in A will be what I'm comparing to the list. There is a fourth character that determines further details about the item that are not pertinent to this issue.
I need to match the value in column A to one of the four groups of values below and have the correct color display in column F. The correct color would be the name of the list that the value is found in.
Blue (K2:K61) contains the values B01 through B25 and C01 through C25.
Red (L2:L80) contains the values F01 to F21, G01 to G23 and H01 to H35.
Yellow (M2:M95) contains the values B36 to B65, C45 to C65 and D23 to D65.
Green (N2:N124) contains the values E36 to E65, F34 to F65, G34 to G65 and H37 to H65.
It probably boils down to a nested IF statement that checks to see if LEFT(A2,3) is found in any of the four lists, but I just can't wrap my head around how to write it.
I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?
View 5 Replies View RelatedIs it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.
combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?
I have a .csv open in Excel 2007 that looks like the following
Code:
1 566
1 566
1 566
2 341
2 341
3 566
3 566
3 566
3 566
...
There is a one-to-one relationship between Column A and B, but B is not unique (but can only take a small set of valid values). I wish to query how many id's (Column A) contain a particular property (Column B). If the example above ended before the "...", I would like to get as the output:
Code:
566 2
341 1
because the property 566 is owned by two id's (1 and 3) and the property 341 is only owned by the id 2.
I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.
1
1
2
3
4
[code].....
I have a sheet of over 60000 rows a small section of which I attach..
I need a formula in cell d2 to give the total for c2 to c10,d3 to d10 will contain this same total...cell a11 starts the next group and the formula for the group total will be in d11 to d18 etc
I have work sheet, ("Sheet1"), there are about 17000 plus entries in two columns A and B,. In Column C, following formula is inserted to show the "number of occurrences" of a Group in Column A.
=IF(ISBLANK(F4),"", IF(COUNTIF($F$2:$F$118,F4)=1, "", COUNTIF($F$2:$F$118,F4) & " occurances"))
Mine active worksheet is Sheet2. In Column A, an entry is made and Column B fetches the equivalent entry from Column Sheet1:B. The formula is :
=IF(ISNUMBER(MATCH(A1, Sheet1!$A:$A,0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$A:$A, 0), 2),
IF(ISNUMBER(MATCH(A1, Sheet1!$B:$B, 0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$B:$B, 0), 1),""))
When an entry will be made in Column A of Sheet 2, naturally it brings the equal of Column B. What I will like that in the subsequent columns, C,D,E,F,G,H, etc. the values may be reflected for all the occurences equal to entered in A1. For example if in Sheet 1, :
1234 xyzxyz
1234 bxyxdx
1234 ffffffff
1234 ggggg
So these are four occurences. in Sheet2, when one entry of 1234 will be made it will show any of the four values, the rest three values may be reflected in subsequent columns.
i am setting up a work sheet on ages for a sporting team
i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group
eg
14-3-00 would go to 7 yrs then go to under 8's
Here's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
VBA code. My sales data is arranged as months in columns and customers in rows. I have about 36 months of data that must be formatted as a single date column for pivot table manipulation.
View 9 Replies View RelatedWhat I want is to sum any/all groups of numbers larger than 0 that are grouped in 4 or more....e.g. in my example D1:G1. The answer should be 150.... I'm trying this formula: ...
View 9 Replies View RelatedI have a set of 13 groups, each with anywhere from 1-9 different variables for a total of 56 variables. I want to find all the possible combinations of 1-5 variables. Only one variable can be used from each group in a combination. Please see attached data sheet.
View 5 Replies View RelatedI've been trying to rank by groups within one list of data; I can sort by two columns and rank with a simple "if" statement, but this is no longer practical for how often the data is updated and re-sorted. Trying to find a dynamic formula that creates ranks by the groups in a selected column. Example attached.
View 5 Replies View RelatedAttached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.
I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E