# Turn Repeating Row Values Into Combined Columns?

May 22, 2014
I have 1 column of pasted values that basically look like this

c

1:a

2:b

3:c

1:d

2:e

3:f etc...

And they need to look like this:

c1 c2 c3

a b c

d e f

I know how to separate the first column into two columns by separating the values by the colon. But how do I rearrange the values as above and combine all "1"s in c1 so it becomes the headers and the column is populated only with the values? The table is too big to do manually (thousands of rows).

View 1 Replies
ADVERTISEMENT
Apr 20, 2006

sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.

My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.

View 9 Replies
View Related
Apr 30, 2014

I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.

I have create a spreadsheet with the data and the result that I typed into the "result" column.

View 3 Replies
View Related
Jan 31, 2007

I have several columns of data representing a shop operation along different years. Now I try to see how will it look like if I have 10 shops like this, all summed up. The problem is that each shop will have the same numbers as the other 9, just that it's first year of operation will be different.

Now, I don't want to have different sheet for each and every shop since their data is exactly the same. I only want to have 1 type of shop which I can add to a consolidated sheet but 10 times, and each time the first year shoud be different.

Just an example: If I have this data for my shop:

YR1 YR2 YR3 YR4

Sales: 10 12 14 16

Costs: 5 6 7 8

Operation: 2 2 3 3

Tax: 1 1 1 1

profit: 2 3 3 4

And lets say I have 4 shops opening in 2000,2001 and 2 in 2002 than my consolidated should look like this:

2000 2001 2002 2003

Sales: 10 12+10 14+12+10*2 16+14+12*2

Costs: 5 6+5 7+6+5*2 8+7+6*2

Operation: 2 2+2 3+2+2*2 3+3+2*2

Tax: 1 1+1 1+1+1*2 1+1+1*2

profit: 2 3+2 3+3+2*2 4+3+3*2

But again, I don't want to open a different table for each shop and than sum them up - I want to have only my one shop, and have a possibility to say when will I open my next shop and the consolidation will be automatically.

View 11 Replies
View Related
Jul 11, 2012

In Column 'A' I have part numbers. In Column B I have suppliers that supply that part number. My report drops into excel as per the below table. My issue is if a part number can be supplied by more than one supplier it duplicates the part number in Column 'A' based on how many suppliers can supply that part number In Column 'B' it will list each supplier for that part number In the table below you can see that part number 1122 can be supplied by supplier 'ABC' and also 'DEF' - Part number 9966 can be supplied by 3 different suppliers so lists the part number 3 times in Column 'A' and in column 'B' it lists each supplier i.e. 'TUV', 'WX' and 'YZ'

What I want to be able to do is the suppliers in Column 'B' be listed in the cells to the right of the part number and dupliactions in Column 'A' be removed

'A' 'B'1122

ABC

1122

DEF

9966

TUV

9966

WX

9966

YZ

View 9 Replies
View Related
Jan 5, 2013

Okay, so this is what I am using:

[Code] .......

It is finding the name for a value from a table of values, the only problem is that there are more than one names which correspond to the same value. It doesnt matter to me which name it retrieves, but it lists the same name over and over again, where i'd prefer if it used a different name each time. Any way to keep it from using the same one?

Example1 (pcb).xlsâ€Ž

View 12 Replies
View Related
Nov 22, 2013

CartonNo1

ProductNo1

LotNo1

LotNo2

CartonNo2

ProductNo1

LotNo3

LotNo4

In the above (repeating for say, 50 or more cartons), is there a way with a macro(I know nothing about) or pivot table(see macro) to get the following format/result?

CartonNo1

ProductNo1

LotNo1

CartonNo1

ProductNo1

LotNo2

[code].....

View 6 Replies
View Related
Jun 13, 2008

I am trying to do something that is probably impossible, but I figured if anyone could come up with a solution, it would be you folks here at Mr. Excel, so here goes.

I'm trying to set up two columns in Excel 2000; for simplicity sake, let’s just call them Column A and Column B. Each row in Column A will contain a code (a combination of letters and numbers) that represents one of 65 different workstations, and they could repeat multiple times. Each row in Column B will contain a number that represents a specific tool, and there are potentially hundreds of different tool numbers.

What I am trying to accomplish is to have something alert me when any tool number in column B appears alongside more than one workstation code in column A. It would really be nice if something like conditional formatting could be used to change the text to red or something when this happened. I know I can use filtering to accomplish this, but with hundreds of different tool codes, filtering just takes too long.

View 9 Replies
View Related
Oct 29, 2009

I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.

I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.

e.g.

444588 Prime Wages/salaries

This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value

Another example would be

444586 Ohd Wages & salaries

This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only

Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.

Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.

View 14 Replies
View Related
Jun 27, 2009

I'm having trouble delivering a MAX value within a range.

In column A I have a running list of numbers, in column B I have a yes or no answer and in column C I'm trying to write a formula that will deliver a MAX value from the A column inbetween occurancies of the Yes and No values from the B column.

I'm trying the SUMIF function but it seems to be proving problematic.

This is what it would look like.

A B C

1 Y 1

2 Y 2

3 Y 3

2 Y 3

1 Y 3

2 N 2

7 N 7

6 N 7

5 N 7

3 N 7

2 N 7

6 Y 6

9 Y 9

3 Y 9

It is almost as if I'm trying to get the formula in the C column only find the latest MAX value from where it currently is to when the last changeover happened from Y to N.

I can't find the Max dependent on a yes or no answer, but I'm struggling to make it only look at the last batch of Yes's or No's!

View 9 Replies
View Related
Feb 15, 2010

It has several steps and i will now just explain the least complicated one, and hopefully once i get this done i will figure out the rest myself. If not then i will need some more help

I have 500 entries (names) in column A and each entry has a coresponding category in Column B (Large, Medium, Small).

I want to see how many entries are there for each category, but i need to count the names which appear in the list only once. If the name is entered more than once then i dont need to count it.

I hope i made it clear as i am not able to provide excel table as an example do to security settings.

I am usinng Excel`03

View 9 Replies
View Related
Sep 24, 2006

How can I modify the codes below so that the results in the listbox would reflect only the actual data? Some previous information stays in the listbox making the search inaccurate. I have tried to clear the listbox first before querying another but results are the same.

On the first loop, the queried items are accurately listed in Worksheets("Search"). For example, if the data being searched on has only one result, entries in this sheet would only show one. But when it is shown in the listbox, it at most times show previously queried information.

Also, how can I dynamically adjust the array so that no vertical scrollbar would appear if the queried results fit perfectly in the listbox? The array below would automatically assign 10 rows in the listbox even if the information shown consumes only less than 10 rows.

Option Explicit

Dim MyArray(10, 5)

Public MyData As Range, c As Range

Sub FindCaseNo()

Application.ScreenUpdating = False

Dim FirstAddress As String 'what to find

Dim rSearch As Range 'range to search

Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG As String

Dim fndH, fndI, fndJ, fndK, fndL, fndM, fndN, fndO, fndP, fndQ, fndR, fndS As String

Dim head1, head2, head3, head4, head5 As String ' heading s for list

Dim i, intFind, intCount As Integer

i = 1

View 6 Replies
View Related
Nov 18, 2013

Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?

View 3 Replies
View Related
Nov 25, 2007

working on grouping coordinates because he we wants to simplify data. But he works manually and it eats his time. He has some sheets with data. I suppose there are 7 sheets. Each sheet has coordinates shown on example below. Also there are intervals between pairs of columns, so there will be column A with B, interval of two columns C & D, column E & F and interval of two columns G & H and so on (altogether about 10 columns of coordinates per sheet). Every coordinate begins with argument '1' and increasing gradually by 1 (it is column A). Second column shows values of arguments. But some are repeating, some not. I would like to double these coordinates, where in one argument is only one value (for example these are row 1. and 11.). If there are two the same values I would keep them. But if there will be more values than two, for example row 5. - 8., I would delete row 6. and 7. from the middle. But border coordinates should be kept (row 5. and 8.) and split by moving up lower coordinate to it with rest coordinates. But I want do it separately for every two columns which contains these coordinates, so if I delete coordinates from columns A and B and split boundary coordinates I don't want do it for columns E&F, I&J...

Examle:

Before:

A B

1. 1 1

2. 2 2

3. 3 2

4. 4 2

5. 5 3

6. 6 3

7. 7 3

8. 8 3

9. 9 4

10. 10 4

11. 11 5

12. 12 6

13. 13 6

14. 14 6

15. 15 7

16. 16 7

.. .. ..

After:

A B

1. 1 1

2. 1 1

3. 2 2

4. 4 2

5. 5 3

6. 8 3

7. 9 4

8. 10 4

9. 11 5

10. 11 5

11. 12 6

12. 14 6

13. 15 7

14. 16 7

.. .. ..

View 9 Replies
View Related
Dec 13, 2013

Every so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.

For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula

=10/2, the only value inside is 5.

How do I keep this happening ??? I want the cells to keep the formulas.

View 2 Replies
View Related
May 3, 2007

is there a way or a formula that turns numbers into their respective words:

ie Cell A1: 8500

Cell A2: Eight Thousand Five Hundred

Big ask I guess but its for a banking model so I can't be the first to have considered this

View 4 Replies
View Related
Jun 28, 2013

taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:

{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}

That got me started. I am willing to use a macro. Here is some sample data:

Table 1 has a sample of the info:

IDENTIFIER

CUSTOMER INFO

2000

111111

[Code].....

View 8 Replies
View Related
Oct 23, 2009

Matching Multiple repeating values in one column with another.

I have a three columns of data that I need to map the requird Ids in Col A against multiple repeats in Col C. AS per data below ....

View 6 Replies
View Related
Mar 20, 2014

I have data that is something like this:

Group Assets

1 200

1 300

2 150

3 50

1 500

3 100

I want to create a scattered plot charts based on the data above but I cant get it to work for some reason. I want to have the "Group" on the x-axis and the "Assets" on the y-axis. I don't want to combine the same group number (for example to have group 1 with total assets of 1000, I want to keep it as 200 300 and 500 because I want to show which group is more represented than other group.). I have tried sorting the data by group and also with out sorting but the data doesn't plot at all (everything looks clustered at the 0 level on the x-axis instead of the respective asset level.

View 1 Replies
View Related
Apr 14, 2014

I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.

View 1 Replies
View Related
May 26, 2006

I'm creating several dozen charts all using Logarithmic Scale and while I know you aren't supposed to provide Zero values in the source data this is one project where that can't be helped. My problem is that the Excel Alert keeps popping up to tell me I shouldn't use zeros and it keeps crashing my sheet because it pops up every 10 seconds or so.

My question is: Is there any way to turn off an Excel Error Alert? OR Is there any value I can place in those cells (other than Zero or One) that would stop that troublesome alert from popping up? Just for reference, this is the alert: MICROSOFT EXCEL

Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following: * Enter only positive values (greater than zero) int he cells used to create the chart. * In the chart, click the axis you want to change. On the Format menu, click Selected Axis. Click the Scale tab, and then clear the Logarithmic Scale check box.

View 8 Replies
View Related
Feb 4, 2013

I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.

Attached is an example of the data I want to sort.

Example of data that needs sorting.xlsx

View 1 Replies
View Related
Jul 6, 2006

I have a list of two columns. Here’s an example. The left most column provides the row number.

_ A B

1 1 0

2 2 1

3 2 1

4 3 2

5 4 2.5

6 5 4

7 1 0

8 1 0

9 2 2

Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.

For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.

Here’s what the updated list would look like:

1 1 0

2 2 1

3 2 1

4 3 1.5

5 4 2

6 5 3.5

7 1 0

8 1 0

9 2 1

View 9 Replies
View Related
May 5, 2014

ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....

The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.

View 10 Replies
View Related
Nov 6, 2009

I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of “#DIV//0!”. On my results page this #DIV//0!” makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0!” to “0” or even turn it to a blank cell?

View 5 Replies
View Related
Mar 19, 2014

If I have text in B1 (Apples)

And text in C1 (Bananas)

How do I write a formula in cell A1 that will combine the text of both so that it reads 'Apples Bananas' in cell A1?

Everything I can think of gives me errors.

View 5 Replies
View Related
Dec 10, 2008

can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View 4 Replies
View Related
Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2

12342122

45671112

89101718

11122678

13144544

15162324

17189987

19201215

21221928

1976

2576

2345

4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies
View Related
Nov 15, 2007

I have a workbook with 17 sheets, is there a easy way to get all 17 sheets combined into one sheet?

View 9 Replies
View Related
Jun 28, 2006

I have a formula that uses the max function/formula (I am not sure of the terminology) to summarise the maximum value contained within a cumulative list of monthly expenditure in cells F12 to F60 (i.e MAX(Cashflow!F12:F60)) of a very old sheet I am working on, I was wondering if it was possible to have a cell that displays the profiled expenditure which is displayed in column E adjacent to the cell containing the maximum expenditure.

I have tried using the vlookup formula however combing formulas!

View 4 Replies
View Related