Inserting Columns With Numbers Relative To Previous Data

Jan 19, 2012

the following issue:

I have a spreadsheet of questionnaire responses which range from 1-7

For example:

Respondent Q1

1 4
2 3
3 7
4 6

So each row is a new respondent and each column is their response from the scale.

What I need to do is code the responses into a different form. I need them to be represented as follows:

Respondent Answer1 Answer2 Answer3 Answer4 Answer 5 Answer 6 Answer 7

1 0 0 0 1 0 0 0
2 0 0 1 0 0 0 0

So that each number then represents the place on the scale from which it was chosen.

I tried recording a macro but I think this requires something a lot more complex.

View 3 Replies


Inserting Page Breaks On Relative Rows Based On Data

Oct 29, 2008

Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.

From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.

View 4 Replies View Related

Finding And Inserting Value From Previous List?

May 15, 2014

The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example

Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0

I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...

Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300

As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.

Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...

Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300

My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.

View 14 Replies View Related

How Do I Stop The Previous Data From Being Wiped Out Once I Move On In Columns

Jan 30, 2007


The above formual resides in each cell in row 1 column g inclusive.

The question is when I move over columns the previous column with figure is wiped out because false "no value" becomes true.

How do I stop the previous data from being wiped out once I move on in columns?

View 14 Replies View Related

Inserting Columns With Data

Sep 25, 2012

what i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit

View 3 Replies View Related

Prevent Excel From Adjusting Formulas When Inserting New Columns Of Raw Data

Nov 12, 2013

I have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.

This is my formula;


When I insert a new column at 'A' the formula changes (as below)


How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?

View 2 Replies View Related

Filling In Relative With Name And Numbers?

Feb 17, 2014

I got a list with numbers on Sheet"Bookings"

and so on.

When i fill in a name like commission on Sheet1"Freshbooks"

The number 8000 must be connected with that that it will appear on my last sheet "Ouput"

So to summ it up Sheet1 I fill in commision on table H the number 8000 must apear on sheet"output"

Is this possible in a Formule or in a VBA line???

View 1 Replies View Related

Give Relative Values To Numbers In A Group

Jan 27, 2007

I have groups of data in a spreadsheet, I need to assign a value in column f for each piece of data in column e. The value needs to be a relative value for the rest of the group. For example if one group of data has 10 rows I need values 1 - 10 in column F, 10 being for the largest value in the respective row in column E and 1 being for the smallest value.

Each group does not have the same number of rows.

If need be I can add blank rows so the space between each group is equal, (if you can tell me an easy way to do this I would appreciate it as theres quite alot of data).

View 9 Replies View Related

Clear Two Columns And Keep Relative Address

Oct 21, 2008

A few days ago I had a list of values in column A, and wanted to display in column B, in the cell where the cursor is when the macro is started (let's call it Bn), the sum of values (An:An-11), where n can be any row where there are values to be added.

"XLD" showed me how to clear the values from the column where the cursor is when the macro is started, and how to display in that cell the sum of 12 values in the adjacent column.

View 11 Replies View Related

How To Arange And Count Various Numbers In Previous Column

Jun 4, 2007

Let's say there is a one column with 9 cells. I need in another column to be aranged various numbers in previous column and in another column to be counted every number how many times appears.

Below is one small example of what I need.

4 2 2
8 4 3
10 8 2
4 10 1
8 15 1

View 9 Replies View Related

Generate Repeat Numbers When Compared To Previous Row?

Apr 16, 2012

How do I generate repeat numbers when compared to previous row?


Column No A2, B1, C1, D1, E1
Row no... A1 5 12 23 50 3
Row no... A2 10 21 5 25 6

Repeat number for above is 5


1) 5 cells only. All numbers are integers, no fractions

2) In a row same number is not repeated.

3) It is possible to repeat all numbers in a new run.

View 9 Replies View Related

Macro To Unhide Columns - Call A Macro Then Revert Columns To Previous State

Jul 17, 2014

I have on sheet1 a number (72 at the moment) of Form CheckBoxes.

In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)

Then, go through and Check All checkboxes

Call MyMacro

Once MyMacro is complete (Filtering & Printing)

Revert the checkboxes to their original state.

The purpose of the checkboxes:

When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden

Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.

View 11 Replies View Related

Inserting Text Infront Of Numbers

May 18, 2009

Yesterday I got the solution to insert the text by using custom format. Exampe: 112233 to Ab-112233 by using "Ab-"General

But when I tried the same method to inserset the Ab on 11-1122

Like 11-1122 into Ab-11-1122 in same cell, it doesn't work.

View 6 Replies View Related

Inserting Sequential Numbers Into A Column

Oct 1, 2009

I have a column of an undefined number of rows where I need to add item numbers from 1 to however many items there are, starting from A9 downwards.

The last 3 used rows on the sheet contain signatures etc so it should not number the bottom 3 rows.

pretty sure its fairly simple code but i dont have anything similar from previous files that i can re-use to do this :p

just needs a simple

count how many rows are blank from A9 downwards (to say A200)
for num=1 to count do
Cell range(A(9+num) = num

i just dont know the code well enough to write it and make it work :p

View 3 Replies View Related

Automatically Inserting Numbers Into Text

Jul 25, 2008

How can I have a number inserted into text on an excel sheet. for example if I have the number 100 in cell A1 and I want it inserted into the following sentence in sell A2:

You are 100 years old. I want the number to be able to change automatically in this sentence when the number in A1 also changes.

View 9 Replies View Related

Inserting A Text(alphabate) In A List Of Numbers

May 16, 2009

suppsose i have 50 list of numbers in column A. I want to insert a text "AAB-" in whole list. How can I do that.





View 9 Replies View Related

Average Numbers Based On Other Two Columns Of Data

Jan 23, 2014

I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?

View 7 Replies View Related

Sum Alternate Columns Base On Previous Column Entry

Jun 14, 2007

I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.

View 3 Replies View Related

Inserting Minus Sign In Front Of Numbers In A Range

Mar 19, 2009

i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet

i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.

Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.

View 8 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Inserting 2 Columns After Each Column?

Apr 23, 2012

I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.

View 8 Replies View Related

Macro That Allow For Inserting New Columns?

Oct 17, 2012

I have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.

The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).

Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?

Copy of code is as follows:

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _


View 5 Replies View Related

Inserting Breaks In Two Sorted Columns

Jul 13, 2009

I have been struggling to figure this out for several days now and i don't really know where to go from here.

I have 2 columns and i need to break them up at certain points.

The attached excel file should hopefully be clear enough to show what i mean.

The first tab is how the data starts the second tab is how i want it to look.

To clarify i need to break it after each change in the first column. ie from 1 to 2.

Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.

I think the excel file will clear up what i mean.

View 13 Replies View Related

Maintain Range When Inserting Columns

Sep 30, 2009

I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.

I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.

View 9 Replies View Related

Inserting Columns Without Changing Formulas?

Feb 7, 2014

I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?

My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:

Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4]
Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]

I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.

View 1 Replies View Related

VBA Macro Not Inserting Columns Properly

Jul 3, 2008

I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.

For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.

Here is the
Sub Macro1()
' Macro1 Macro
' Macro recorded 6/30/2008 by kmayfield
ActiveCell.FormulaR1C1 = "Jan-2008"
ActiveCell.FormulaR1C1 = "Feb-2008"
ActiveCell.FormulaR1C1 = "Mar-2008"
ActiveCell.FormulaR1C1 = "Apr-2008"
ActiveCell.FormulaR1C1 = "May-2008"

View 9 Replies View Related

Find Last Previous Non Blank Value And Summarize Previous 6 Months?

Dec 10, 2012

see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:

Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012

View 7 Replies View Related

Excel Crashing When Inserting / Deleting New Columns?

Jul 21, 2014

I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.

View 1 Replies View Related

Prevent The Users From Inserting Columns Within A Certain Range?

Jan 7, 2010

I am instantiating Excel 2003 from and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?

View 2 Replies View Related

Inserting Columns On Worksheet And Keeping Formula?

Jan 10, 2012

I have a spreadsheet that I can modify. It currently has a three product column but I need to insert 2 or 3 more columns to make 5 plus the total at the end. It also have a summary sheet. I insert the 2 columns and somehow got the formulas flowing. However when I hit the summary page its not showing results for those two new columns in the total. I am lost. I wish I could post the sheet. Its a multiple product break-even analysis exercise.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved