I have a mix of information in column C. There are headings with data below them. I need to pull the headings into column A beside the data. I also need to pull the corresponding data from column D into column B.

I can distinguish the headings from the data by length. The headings are of various lengths (longer than 12 characters), but the data below it is always 12 characters long. So if the length of the data in cell C4 is not equal to 12, it's a heading. I don't need to put anything into A4 and B4 because I want to delete headings later.

So I move onto cell C5. If the length of cell C5 is equal to 12, then I want the last heading (from C4) copied into cell A5 and the name of the heading (from D4) copied into cell B5.

And so on - if the length of cell C6 is equal to 12, then the data from C4 would be copied into cell A6 and the data from D4 would be copied into cell B6. If the length of cell C6 is not equal to 12, this would be my new heading to use for the cells below (until a new heading is found).

This should continue until it finds a cell in column C that is blank.

Then delete the headings (which should not have data in columns A and B).

This would always begin with the first heading in C4 and the name of the heading in cell D4.

Thanks for your help!!

Kat

I can distinguish the headings from the data by length. The headings are of various lengths (longer than 12 characters), but the data below it is always 12 characters long. So if the length of the data in cell C4 is not equal to 12, it's a heading. I don't need to put anything into A4 and B4 because I want to delete headings later.

So I move onto cell C5. If the length of cell C5 is equal to 12, then I want the last heading (from C4) copied into cell A5 and the name of the heading (from D4) copied into cell B5.

And so on - if the length of cell C6 is equal to 12, then the data from C4 would be copied into cell A6 and the data from D4 would be copied into cell B6. If the length of cell C6 is not equal to 12, this would be my new heading to use for the cells below (until a new heading is found).

This should continue until it finds a cell in column C that is blank.

Then delete the headings (which should not have data in columns A and B).

This would always begin with the first heading in C4 and the name of the heading in cell D4.

Thanks for your help!!

Kat

- I need to analyze last value of each row and get top 10 of repeating values
- Sum Cells Of Each Row In Named Range
- How can I take the last few columns of each row an move them further to the right?
- Putting column headings on the x-axis of each line chart
- Putting column headings on the x-axis of each line chart
- To display a column for each date under current period of selection and count result
- Append text to the begining of each row in column A
- Vlookup when value/text is not in left-most column
- Detecting relationship between values/cells of each row
- Help needed to add a column of time ( Days,Hours, Mins )
- Copy all relevant cells from each row to another worksheet, cells may differ by row
- Need macro to parse/paste column of data
- Macro Code To Average Multiple List Of Varying Column Ranges
- Copy 7 Columns to next blank column, but rotate rows
- Excel Formula or tool to compare two columns of data
- Trying to combine 2 columns of data to create one x axis in graph
- Offset from a variable column to a fixed column
- Excel Formula or tool to compare two columns of data
- How to copy every nth column of a row to the first cell of the row?
- Linking a monthly total to the last date of each month
- Home Key Code: Go To 1st Column of Active Row
- Macro to replace different text in each row
- Formulas: Need to compare 3 columns of data with sp
- Trying to add 10 to an entire column of numbers

For example

A B C D E F G H I J

-------------------------------

1|112 4 8 ...... 3

2|55 54 ...... 3

3|1 7 3 2............9

4|82 977 15................................10

5|45 477............................................9

6|77 2244..........................9

7|557 4458..............................9

8|78 428...................................10

9|44 326.........................10

In result I want to get TOP

1. 9 (4 times)

2. 10 (3 times)

3. 3 (2 times)

Example:

A named range called "MyRng" that's 3 rows by 5 columns -

1 1 1 1 2

1 1 1 1 3

1 1 1 1 4I need three cells that give me the sums of each row -

6

7

8-----

Just figured it out.

= SUM(OFFSET(MyRng,0,0,1,COLUMNS(MyRng)))

= SUM(OFFSET(MyRng,1,0,1,COLUMNS(MyRng)))

= SUM(OFFSET(MyRng,2,0,1,COLUMNS(MyRng)))

Thanks.

I need help with a very simple macro. Never really programmed in Excel so I hope a friendly user can help me out with this.

I have a huge output from a software, where the number of filled cells to the right varies. I need to get the last 5 cells on each row, and move these so that they all start with the same column. A hands on example:

(Original output)

5 4 5 7 9 6 4 6 7 5 3 3

4 5 6 4 3 5 6

4 5 6 7 6 8 5 5 4

.. needs to be (the forum didn't like spaces, so i've replaced a space with a dash):

5 4 5 7 9 6 4 ----------- 6 7 5 3 3

4 5 ------------------------ 6 4 3 5 6

4 5 6 7 ------------------- 6 8 5 5 4

So, we take the last 5 cells on each row and move them i.e 100 cells to the right.

Thank you in advance guys.

/J

I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).

The problem that i am facing is, How to put column headings on the x-axis of each chart.

NOTE: Row heading is already appearing on the y-axis of each chart.

I have also attached my excel sheet...Here is my code:

VB:LineCharts() Dim Ws As Worksheet Dim NewWs As Worksheet Dim cht As Chart Dim LastRow As Long Dim CurrRow As Long Set Ws = ThisWorkbook.Worksheets("Sheet1") LastRow = Ws.Range("A65536").End(xlUp).Row For CurrRow = 2 To LastRow Set NewWs = ThisWorkbook.Worksheets.Add NewWs.Name = Ws.Range("A" & CurrRow).Value Set cht = ThisWorkbook.Charts.Add With cht .ChartType = xlLine .SeriesCollection.NewSeries .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" .SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2" .Location Where:=xlLocationAsObject, Name:=NewWs.Name End With Next CurrRow End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).

The problem that i am facing is, How to put column headings on the x-axis of each chart.

NOTE: Row heading is already appearing on the y-axis of each chart.

I have also attached my excel sheet for better understanding of my problem.....I want to display DP1, DP2, DP3,... so on on the X-axis of each line graph/chart. Here is my code:

Sub LineCharts()

Dim Ws As Worksheet

Dim NewWs As Worksheet

Dim cht As Chart

Dim LastRow As Long

Dim CurrRow As Long

Set Ws = ThisWorkbook.Worksheets("Sheet1")

LastRow = Ws.Range("A65536").End(xlUp).Row

For CurrRow = 2 To LastRow

Set NewWs = ThisWorkbook.Worksheets.Add

NewWs.Name = Ws.Range("A" & CurrRow).Value

Set cht = ThisWorkbook.Charts.Add

With cht

.ChartType = xlLine

.SeriesCollection.NewSeries

.SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"

.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"

.Location Where:=xlLocationAsObject, Name:=NewWs.Name

End With

Next CurrRow

End Sub

My Query is :

VB:The result is :No_of_Units FROM vplan_activite va inner join glpi_users gu on va.vact_ressource = gu.ID inner join plan_tache pt on va.vact_tache = pt.tch_id inner join plan_projet pj on pt.tch_projet = pj.prj_id inner join plan_type_demande ptd on pt.tch_demande = ptd.dmd_id WHERE vact_status = 'R' And name Like 'santhosh' And vact_date between '2011-03-01' and '2010-03-31' GROUP BY name, prj_code_imput, prj_nom_imput, prj_id, gu.ID Order by prj_id ascIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

ID name prj_id prj_nom_imput prj_code_imput vact_date No_of_Units

111 Santhosh 29 Project x 014EC10467300 3/1/2011 5

111 Santhosh 32 Project y 014EC10158500 3/1/2011 2

111 Santhosh 43 Project xy 014EC11015400 3/1/2011 1

111 Santhosh 60 Project yz 680007 3/2/2011 152

111 Santhosh 78 Project a 085 3/9/2011 10

111 Santhosh 83 Project ab 014EC11520600 3/1/2011 5

111 Santhosh 93 Project bc 999999 3/2/2011 38

111 Santhosh 117 Project b 014EC10521900 3/1/2011 1

111 Santhosh 150 Project ca 014EC11724700 3/1/2011 1

111 Santhosh 161 Project c 014EC11858300 3/1/2011 2

111 Santhosh 162 Project d 014EC11696700 3/23/2011 3

Requirement :

The last column shows the no of units (1unit = 45 mins and 10 units = 1 day) for the current period in selection booked by the particular resource in the second column. The 6th Column (Vact_Date) shows the date from which the resource started booking for the current period under selection.

My Requirement is to display a column for each date under current period of selection and count the no of units for the particular date with respect to the Prj_nom_imput.

Kindly let me know the procedure and code for this? I will elaborate more if my explanation was not clear.

Thanks in Advance.

I am trying to create a macro that will append an 'A.' to the begining of each row that has a value in Column A. I tried recording a macro but it does not do exactly what I need.

Thanks,

GJ

I have a list of possible text values (cells B5:B223) and want to find if any of these values occur in a table_array (cells C5:H223) which has plenty of empty cells. My understanding is that vlookup will search the left-most column (C), but what if some values don't appear in column C but appear for the first time in column D or H?

My goal is to count the number of entries from column B that have at least one occurrence in the table_array.

This is what i have so far:

=IF(ISERROR(VLOOKUP(B5,$C$5:$H$223,1,FALSE))," ",VLOOKUP(B5,$C$5:$H$223,1,FALSE))

Any help greatly welcomed.

For Example:

I have two worksheets say Sheet1 and Sheet2.

Sheet1 data looks like this:

100 101 102 200 201 202

Here first column is the heading of each row, i.e., 100, 200.

Second and third column are the related values of each header row, i.e., 101 and 102 are the related values of 100, and similarly 201 and 202 are the related values of 200.

Sheet2 data looks like this:

100 200 101 102 201 202 100 1 0 1 1 0 0 200 0 1 0 0 1 1 101 1 0 1 1 0 0 102 1 0 1 1 0 0 201 0 1 0 0 1 1 202 0 1 0 0 1 1 Here, I wrote all the data found in Sheet1 as row heading and column heading. I assume this dataset as a matrix. Now I want to fill this matrix by using the relationship. I have to fill it by using 0 and 1. If data is matched then 1 otherwise 0.

Relationship Definition:

In Sheet1, all the data written in row1, i.e., 100, 101 and 102 are related to each other. Now in Sheet2, I have to fill 1 against 100*100, 100*101 and 100*102 and all the other entries to 0.

and similarly all the data written in row2 are related to each other. Now in Sheet2, I have to fill 1 against 200*200, 200*201 and 200*202 and all the other entries to 0 and so on.

I have a huge amount of data to do this job. Any solution using macro will be greatly appreciated.

Thanks,

Not good with time formulas and had some great help recently from Trunten with the following formula (to get time taken based on distance and speed ) which displays results in Days / Hours / Minutes.

=TEXT(((100/1.85)/24),"d days hh:mm:")

1.85 kph means that in 1 hour you travel 1.85km: 1.85 = 1.85 * 1 (distance = speed * time)

t = d / s

t = 100 / 0.925

t = 108.11 hours (2dp) = 4 days 12 hours 06 minutes 29 seconds

Now i have used this formula to give an estimate of time it will take at varying speeds to travel the distance over lengths of pipes numbered 1-10 ( all differing lengths in Km )

What i need to do now is total the columns of each speed ( these cells now display for example 1 Day 12 hours 10 mins )

Can anyone assist?

I have attached a small version of the worksheet to show the sort of thing i am trying to do.

Best Rgds

SubC

In the meantime I thought I would post as well just in case I have no luck.

I have a spreadsheet that is about 40 columns wide. Each row contains a members data, address, numbers, etc, then children's names and birthdates.

Currently the relevant data on this main sheets is entered: Column M=Child 1 Name, Column N=Child 1 Bday, Column O=Child 2 Name, Column P=Child 2 Bday, etc up to Child 7 bday. Obviously some members have only one child and some have more so some columns of each row are blank.

I would like to copy all the child data to a seperate worksheet into one column.

That would contain, Last Name (Column A on Main Data tab), then Child # Name, Child # Bday.

It would be nice if this could be updated (overwrite, not append) everytime the spreadsheet was opened but that is not necessary.

I have done some vb code in excel before but mostly editing not creating, I have not done anything with macros.

I'm not really sure which this should/would be.

Thanks for any assistance.

example.xlsx

100

101

102

103

104

I eventually need to end up with the following:

100

101

102

103

104

I would prefer if this macro were dynamic. Sometimes I have only 20 rows of numbers, but sometimes I have up to 150 rows. Thanks!

I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:

1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.

2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).

I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.

So far, I am working on code to try to "find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.

Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.

I have attached a version of the file that displays how I want it to look.

The code I have thus far is:

VB:Any and all help is appreciated. If I should completely rethink my approach to getting these results, I am open to that as well.Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).ActivateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have 7 columns, which represent 7 Days of work. Which start from Column D.

There may be 20 rows of work, which represent 20 people, this may vary at time, could be 7, or 12 or 30 at times.

I would like to be able to, using the example of 20 rows, is to copy rows 2 to 19 to row 1 to the next blank column,then, copy row 1 to row 20, to the bottom of what was just copied

Then repeat the process, but moving the top line to the bottom each time, and each row move up, until the original line 1 gets to the top line again.

If I only had 4 rows, it would rotate the rows 4 times. 10 rows rotate 10 times.

Then be able to copy the 20 columns to the next blank column. Then the next blank column. Until the end of the spreadsheet

Hopefully to make a row show the working, across the sheet.

Any help appreciated

Cfer

put a result in another column. Basically: "If the number in column A (each

row will have a different number) appears in column E (any row and may even

have multiple occurences), put a Y in column G on the row in which the column

A number appears." Does that even make sense?

create an Asthma peak flow chart and have 4 different times per day with

figures - before & after), so each day has 4 lines. I need to show data and

graph (if possible) on one sheet to take to the hospital. At the moment the

column is too long to be readable in landscape (with graph) and I can't find

a way to change orientation of graph to make it readable in portrait. Is

anything possible (my dates are like 01 8, 01 12, 01 16, 01 20 etc. for

every day in the month).

row, and then move it to another cell in the same row. The originating cell

is going to vary, but the receiving cell is going to be in a constant column

in the same row. I know about using offset to move over a specified number of

cells, but I'm not certain as to how many cells I'm going to want to move

over to get to the same column in each row that I work with. Is there a way

to use offset to move into a constant column?

put a result in another column. Basically: "If the number in column A (each

row will have a different number) appears in column E (any row and may even

have multiple occurences), put a Y in column G on the row in which the column

A number appears." Does that even make sense?

The values in the columns are string values and I'd like to place a

space between every value as it's inserted into the cell.

There are thousands of columns (using excel 2007) and each row is using

a different number of columns, so the macro will need to figure out

when the last column of data is in the row and stop at that point.

And if so: the dates running down Column A miss out sundays, what happens if the month ends on a sunday?

=('[ID111.xls]Sheet1'!$A$1)

I am retrieving information from 1,000 workbooks and have repeated the above formula across 1,000 rows. I need to replace the name of the workbook [ID111.xls] in each row with the unique name of each workbook. Workbook names are entered in column A. For example:

A B

----------------------------------

ID135 =('[ID111.xls]Sheet1'!$A$1)

ID954 =('[ID111.xls]Sheet1'!$A$1)

ID238 =('[ID111.xls]Sheet1'!$A$1)

I hope to find/develop a macro to search for Sheet1 in each row and replace it with the text in column A. The final result would look like:

A B

----------------------------------

ID135 =('[ID135.xls]Sheet1'!$A$1)

ID954 =('[ID954.xls]Sheet1'!$A$1)

ID238 =('[ID238.xls]Sheet1'!$A$1)

I am a macro novice. Any and all help and advice is sincerely appreciated.

Thank you

Column A = Executive Name

Column K = Resource Type (Employee, Contractor, Vacant, etc)

Column X = State (AL, CA, FL, etc)

I need a count of the number of vacancies working for Executive Smith that are in a set of States.

What I've tried repeatedly is an Array. I know you discourage this but I'm under a deadline....surprise ;-)

My formula(s) have looked something like this:

{SUM=(('Sheet Name'!$A$2:$A$2000="Smith")*('Sheet Name'!$K$2:$K$2000="Vacant")*(............

I've tried OR functions at the end, no luck.

I've tried creating a new column on another sheet that performed an IF

statement that created a new Column that represented the set of States I

need, but still no luck.

I've attached a very small sample of the file I'm using. Should give you an idea of the formatting and data.

Thank you,

Aaron

I have a feeling this is very simple, and I still can't figure it out