Free Microsoft Excel 2013 Quick Reference

Move Headings To Left-Most Column Of Each Row

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


Post your answer or comment

comments powered by Disqus
I need to analyze last value of each row and get top 10 of repeating values

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)

I need to sum the cells of each row in a named range. Any ideas?

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.

Hi guys,

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

Hi Guys,
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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Guys,
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

I have a doubt with the following query. Could anyone please help me in finding a solution.

My Query is :


	VB:
	
 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 asc 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The result is :

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.

Excel 2007

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

Hi, i have spent a day thinking about this, haven't found a solution.

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.

Hi, I want to detect the relationship between values of each row.
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,

Hi
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

I have made notes from several posts that are close and I am going to try come up with a solution by combining them.
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

I'm trying to record a macro that will take a simple column of Excel data and parse it out into individual lines (similar to a tab-delimited file, except with instead of . I need to paste this column of data, entry by entry, into a terminal program (Citrix ICA Client), with a short delay (for terminal response) between each line. This seems like it should only need a simple macro to complete, but I'm not very efficient with macros. Maybe I first need to save as a tab-delimited file--but then what? For example, I ORIGINALLY have this column of data in Excel:

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!

So here is my dilemma,
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:
	
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
False, SearchFormat:=False).Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any and all help is appreciated. If I should completely rethink my approach to getting these results, I am open to that as well.

Hi,

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

I am looking for a formula in excel that will compare to columns of data and
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?

I am trying to combine 2 columns of data to create on x axis (I have to
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).

I'm trying to put together a macro that will take a value from one cell in a
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?

I am looking for a formula in excel that will compare to columns of data and
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?

How do you copy every nth column of a row to the first cell of the row?

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.

In this spreadsheet (attatched) I wish to create totals in columns D and E which are linked to the last date of each month in Column A. Is there a formula I can create so that it will automatically add together totals on the last day of each month.?
And if so: the dates running down Column A miss out sundays, what happens if the month ends on a sunday?

I want to be able to return to the first column of the current row. In spreadsheet mode i can use the HOME key. How is this acheived in Excel VBA?

I am retrieving information from multiple workbooks into a single summary workbook. I have entered a formula to reference the desired information from one workbook into the summary workbook; for example:

=('[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

I need to compare three columns of data.

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

I am not very familure will excel, I have 2003 and i am trying to add a number like 10 to an entire column of numbers. any help would be appriciated thanks


No luck finding an answer? You could always try Google.