Over 1 million tech questions and answers.

Move data from column to row based on value of cell?

Q: Move data from column to row based on value of cell?

I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:

COLUMN A COLUMNB

AV_ACTN_RSLT_VWACTN_RSLT_CDAV_ACTN_RSLT_VWDESCRAV_ACTN_RSLT_VWDESCRSHORTAV_ACTN_RSP_VWEMPLIDAV_ACTN_RSP_VWEXT_ORG_IDAV_ACTN_RSP_VWINSTITUTIONAV_ACTN_RSP_VWNAMEAV_ACTN_RSP_VWRSPL_ID

AMT_X_SAL_TYPEWCS_PLAN_DESCR20AMT_X_SAL_TYPEWCS_TOT_AMOUNT

I need to convert the field names so that they are in a row like this:

AMT_X_SAL_TYPEWCS_PLAN_DESCR20WCS_TOT_AMOUNT

Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

Or maybe i should dump the spreadsheet in access and try there...

Thanks,

Amy

RELEVANCY SCORE 200
Preferred Solution: Move data from column to row based on value of cell?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Move data from column to row based on value of cell?

Read other 6 answers
RELEVANCY SCORE 88.4

Hello,
I have a very large spreadsheet of data, 365 columns and 290 rows. I would like to convert the data into 1 very long column, with all of the data consecutively listed. Is there a function that allows me to tell the formula to look to the first line of the next row of data, and copy down from there, and then go to the next column, etc? Right now, I'm just referencing the first cell in each column, dragging down to fill the 290 following cells, and then referencing the next column. It's really time consuming because I have 48 of these spreadsheets. Any suggestions would be greatly appreciated!
 

A:Solved: How to move data in a series of column into one column

Hi,

if you name the range of 365x290 cells as, for example, 'MyRange', then the following (downwards extendable) formula should work:

=OFFSET(INDEX(MyRange,1,1),MOD(ROW(A1)-1,ROWS(MyRange)),(INT(ROW(A1)-1)/ROWS(MyRange)))

Jimmy
 

Read other 2 answers
RELEVANCY SCORE 88

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?
 

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers
RELEVANCY SCORE 88

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).
 

Read other answers
RELEVANCY SCORE 86.8

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 86.8

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 80

Afternoon. I would like to build a query in Access 2010 to move some data from one field to another.

I only want to move some data though.
My fields are named mailadd1 and mailadd2
The data that needs to be moved is in mailadd1.

These are addresses. The first field should be the street address, the second should contain the city, state, and zip.

This is correct on a lot of the rows, but on about half of the 109K records, the data that should be in mailadd2 is in mailadd1 and mailadd2 is blank.

So I need to move the data from mail1 to mail2 IF mail2 is empty. In the following example the first row and the last row's data need to be moved, the middle two rows are fine.

Again, mailadd2 is always blank if the data needs to be moved, if that helps.

Thanks in advance for any help you can offer.
 

Read other answers
RELEVANCY SCORE 79.2

Hi,
I want to lock a range of cell ( Column) after data entry. Cell should lock automatically.

for example if D2= today(), then range of cell from D3 to D20000 is ediatble otherwise cell should be blocked.

No body can touch cell for yesterday or tomorrow...

cell should be open only for the date...

pl help, its urgent
[email protected]
 

Read other answers
RELEVANCY SCORE 77.6

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

A:Excel Macro to Move data in Rows to column

Read other 8 answers
RELEVANCY SCORE 77.6

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

Read other 2 answers
RELEVANCY SCORE 77.2

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.
Jose
 

Read other answers
RELEVANCY SCORE 76.8

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 76.8

I have a long list of contacts in Column A.

They look like this:

Acme Company
123 Easy Street,
Easytown, USA 00012
www.acmecompany.com

Boohah Company
345 Easy Street,
Anytown, USA 00013
www.BoohahCompany.com

etc... for 200 lines in Col A.

I need to copy the URL address in this list and move them to Col B.

My goal is to have a single list of all URL address.

Can this be doen in Excel?

Thanks,
Bob
 

A:Excel: In Col. A, find cell with text string "www."and move it to column B.

in B1 put
=IF( Left(A1,3)="WWW", mid(A1,5,200) , "")

That will copy the url to column B
if you want to have then all together and not linked to the original column
I would then click on B
Copy
and >paste >special >text
back to column B
That now breaks the link to column A
and then , I would just do a sort on column B to get all together

Otherwise the formula will be a little more complex to remove the blank rows
 

Read other 10 answers
RELEVANCY SCORE 76.8

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.

151
183
2001

results = "151","183","2001"
 

A:Solved: Excel Column of data place in one cell with "data", "data" results

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

http://www.asap-utilities.com/

(edit: "entire column" -- do you mean full from top to bottom? )
 

Read other 2 answers
RELEVANCY SCORE 76.4

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 76.4

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

Read other 3 answers
RELEVANCY SCORE 76.4

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

A:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

Read other 3 answers
RELEVANCY SCORE 76.4

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

Read other answers
RELEVANCY SCORE 74.4

i have all the latest versions of excell and windows. I need to know how to require data entry in a cell without allowing to go to next cell without entering data, for multiple cells. I already did data validation, locked all other cells. and how to i make it so i can choose which cell it starts at and goes to next?
 

A:how do i force data entry before allowing to move to next cell

Read other 16 answers
RELEVANCY SCORE 74.4

I work with Excel 2007. If under column A, I have 900 names of Restaurants, and under each name of restaurant, is a one-line address of the restaurant, what should be done so that the address can be moved to the cell to the right of the restaurant.

For example

This is the original data:

Column A
AAA Restaurant
AAA address
BBB Restaurant
BBB address
CCC Restaurant
CCC address
This is the desired result

Column A Column B
AAA Restaurant AAA address
BBB Restaurant BBB address
CCC Restaurant CCC address

Thanks is advance
 

A:Solved: Excel 2007 Move Data to Another Cell

Read other 7 answers
RELEVANCY SCORE 73.2

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....
Example:
Everytime column a = "Jones", check if column e = "tom".....if so count it.
 

A:Counting strings in one column based on Condition in another column in Excel

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

Read other 3 answers
RELEVANCY SCORE 72.8

Hoping to get some help on this question please. I have reviewed the forum and found a similar post, but the sample data is more predictable than mine and the Macro posted doesn't work on my data. See post "Excel Macro to Move data in Rows to column" 28-Aug-2008, 05:47 AM".

On my data, I would like to be able to take the yellow row, and move the data up to the previous row starting in column "R". The rows I would like on the same line, all begin with a date. I don't know how to make a looping Macro that will identify the "date lines", and take the "second date line", and move it to the "first date line's" row.

Any help would be appreciated.
I have attached some sample data. (I hope that's okay?!)

Thank you!!!!
Christine

PS - The range on data varies.
 

A:Solved: Excel Macro to sort data by date, then move to new cell

Christine,

this macro below works well with the sample data.
Code:
Sub sorting()
Dim Rng As Range, c As Range, i As Long
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In Rng
i = (i + 1) Mod 2
If i = 0 Then
With c.Resize(, 15)
.Copy .Offset(-1, 17)
.ClearContents
End With
End If
Next
End Sub

How it works:
1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
2. In a loop, it takes every second cell of the set of cells selected in Step #1.
3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
4. Deletes the content of the original 15 cells wide region.

Caution:
1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
3. Don't run it twice on the same worksheet.
4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

Jimmy
 

Read other 3 answers
RELEVANCY SCORE 72.4

I have an Excel 2003 spreadsheet that has three columns that are populated by dropdown lists. I have attached a sample based on my real problem for you to see. Depending on the selection in Column A there could be a required entry in column B or a required entry in column C or it may require that an entry be made into either B or C. If an entry is made in column A and none made in B or C then I need a message box to pop up to remind them to make the additional selection. Thanks in advance for your assistance.
 

Read other answers
RELEVANCY SCORE 71.2

I have a very large spreadsheet with thousands of rows and about 20 columns
Col A has either of 4 values North, South, East, West
The other Cols have numbers
I want to get a total for North, South, East, West for Col B, C etc
Is there a formula I can use something like (if Col A = North , total ColB is )
Don't want to keep resorting by Cola and putting in Subtotals
Hope I have explained it well enough

Thanks

Brian
Dublin, Ireland
 

A:Excel Total in a column based upon value in another Column

yes you can use
SUMIF()

SUMIF(A:A, "north", B:B)

OR if you put the values North, South etc into a new column - Say Z2,Z3,Z4,Z5

SUMIF(A:A, Z2, B:B)
and copy down

if not - please upload a sample
Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
 

Read other 3 answers
RELEVANCY SCORE 70.8

Not sure exactly how to word what I am trying to accomplish. for each matching cell in Column A, evaluate the values in Column B for matching rows. In the example spreadsheet, I have highlighted the ones I want vba to evaluate. The column can also be as large as 5000 rows at times. So for Dayton, at least one % is 0 and one is more than 1%. In that case I want the 0% highlighted. Same for Los Angeles and Annapolis which has 4 matching rows. However, I don't want Sacramento and Mobile 0% value highlighted because the value(s) are all below 1% each. I don't even know where to start on coding this. Any help is greatly appreciated.
 

A:For each matching cell in column A, evaluate Column B value

Read other 6 answers
RELEVANCY SCORE 69.2

Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you

Peg
 

A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.
 

Read other 1 answers
RELEVANCY SCORE 69.2

Hi.

I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?

Cheers
 

Read other answers
RELEVANCY SCORE 68.4

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 68

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers
RELEVANCY SCORE 67.6

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 66.4

Excel 2007 for PC

Rookie Excel user requesting help writing a macro that will overwrite information in one column, based on information in a second column. See attached file.
 

A:Solved: Macro that overwrites info in 1st column based on info in a 2nd column

Read other 7 answers
RELEVANCY SCORE 64.4

I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.
 

A:Solved: update the column in one table based on the column of another table

Read other 16 answers
RELEVANCY SCORE 62.4

For example, sum of every 5th cell in the B column.

At the moment, I am having to add the 5th cell manually to the function. See:

(sum(b5, b10, b15, b20, etc )).

I'm sure there is an easier way to do this but I don't know the syntax. Any help would be greatly appreciated.
 

A:Sum every X cell of a column.

Attached a sample
Code:


Formula in Column C

=IF(ROW()/5 = INT(ROW()/5);1;0)
If the rownr / 5 = integer(rownr/5) then put 1 in C

If C= 1 that measn it's the 5th row
The formula in B1 =IF(C1=1;SUM(A1:A5);"")


This is not taking a header row into account
 

Read other 1 answers
RELEVANCY SCORE 60.8

Hello,
Is it possible in one cell to show the total of both the column and the row? The idea is that it would be a double check because they should add up to the same thing. I am working with Windows 2007.
Thanks.
 

A:Solved: adding row and column in same cell

Read other 9 answers
RELEVANCY SCORE 60.4

I have a number of user forms. Let's say 5. Stage 1,2,3,4 and 5. At stage 1 a serial number is entered into a text box. At stage 2 the serial number becomes available in a combo box. I need to select the serial number from the combo box and press submit. I need the serial number to now show in a combo box at stage 3, BUT, no longer show at stage 2. Once the serial number has been submitted at stage 2 it can't be selected again (unless re-booked in at stage 1).

It is like a production line. Stage by stage. Eventually when it reaches the end at stage 5 and submitted it will be gone from all combo boxes.

Each user form opens up a database (spreadsheet) when submit is pressed. It enters the data and closes the database. I need all the data to remain in the database but the combo boxes to only show the serial numbers available to the stage.

Hope this makes sense, this has bothered me for days, can't get to the bottom of it.
 

A:populating a combobox based on criteria of another column

Read other 16 answers
RELEVANCY SCORE 60.4

Currently I have 83 products listed in one table in Access and a huge listing of people who use those products in another table. When I run a query to find all the ones who use the products listed I get 58113 connections.
What I would like would be to randomly select 10 to 20 users of each product (the minimum number of users for any one product is 10, but the maximum is over 3000 for the most used product. I would then like to export these randomly selected names to different Excel worksheets in the same workbook - I suppose one could export them all and then break up the data into new worksheets based on the product code....

Oh, and there are 10 products for whom there are no listed users, so I will have to match them up by their reporting districts instead and then select 10-20 as well...(I have run a query for this and the smallest group this way is 15 people.)

Thanks in advance!
 

A:Solved: Select some listings based on one column - need VBA

Read other 7 answers
RELEVANCY SCORE 60.4

Hi,
If I have a column A with dollars in it, and column B with dates,
how could I write a formula to give me the total of column A
only when the date in B falls between 8/1/02 and 8/15/02?

I can get it to work if I say "<8/16/02" or ">7/31/02", but I can't get the range syntax....

Thank you!
kimmer
 

Read other answers
RELEVANCY SCORE 60.4

OK I use excel for my budget and I need to exclude certain cells from an autosum as I have credit card balances listed before and after
I have made payments and I also have my savings account listed and so Id like to perform a final autosum to show the total debt

( marked with a negative symbol ) combined with my total savings
thanks
 

A:excel excluding cell or column from autosum

I was thinking of a function like SUM.IF but that won't do.
I think you'll have to dig into vba and write a macro that tests which values to add up.
 

Read other 1 answers
RELEVANCY SCORE 60.4

I am trying to loop through a column and where there are blanks look up the value with vlookup. Initially I tried looping until cell is blank but of course that stopped the loop at the first blank. I changed it to loop until last row and column but I am sure I am not using the right syntax to do it. It loops great through row but continues past the last row. Any help is appreciated.
Code:

Dim UsedRng As Range, LastRow As Long
Set UsedRng = ActiveSheet.UsedRange
LastRow = UsedRng(UsedRng.Cells.Count).Row
Range("H2").Select
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Cells(LastRow, 8)
 

A:Solved: Excel VBA Loop Until Last Cell in column

Code:
Dim UsedRng As Range, LastRow As Long
Set UsedRng = ActiveSheet.UsedRange
LastRow = UsedRng(UsedRng.Cells.Count).Row
Range("H2").Select
Do Until ActiveCell.Row = LastRow + 1
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
You need the until element at the start of the loop and you want the loop to end having processed the last row.
 

Read other 2 answers
RELEVANCY SCORE 60.4

I have a column of data that will be updated weekly with more data. I reference the last value in the column (which is the most current value of the column) using this formula:

=VLOOKUP(9.99999999999999E+307,'Sheet1'!G:G,1,1)

The column is column 'G' obviously.

In another cell, I would like to reference the cell 52 rows above that one (a year ago) and possibly take the average of the values inbetween as well. Any ideas?

I played around with a match/offset but I just got lost.
 

A:Solved: Referencing Cells Above the Last Cell in a Column

Read other 15 answers
RELEVANCY SCORE 60.4

I.e. without multiplying the contents into every cell that I pass?

A:In Excel 2007, can I drag a cell to another column or row?

I know the answer now, it's too go to a border, wait until the cursor changes to the thin crosshairs, and then drag.

Read other 1 answers
RELEVANCY SCORE 60.4

The title says it all.
 

A:Autofit a cell, not a column in Excel 2003

Read other 13 answers
RELEVANCY SCORE 59.6

Hi,
I am trying to use the following macro to copy cells from one worksheet to another.

My problem is that I only want to copy certain column values - is it possible to do this and how would I change the coding - I`m relatively new to programming macros!!

Sub Test2()
Dim PLimit As Long, ALimit As Long
Dim p As Long
PLimit = Sheets("Sales Enquiry").Cells(Rows.Count, 9).End(xlUp).Row
ALimit = Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row + 1
For p = PLimit To 1 Step -1
If Sheets("Sales Enquiry").Cells(p, 9) = "y" Then
Sheets("Sheet1").Range("A2:H2").Insert (xlDown)
Sheets("Sales Enquiry").Range("A" & p & ":H" & p).Copy
Sheets("Sheet1").Range("A2:H2").PasteSpecial (xlValues)
ALimit = ALimit + 1
End If
Next p
End Sub

Thanks for your help in advance,

Ken
 

A:Copying specific column values in a row based on a condition

Read other 15 answers
RELEVANCY SCORE 59.6

I have attached two images of an Excel worksheet. I have gone to the trouble of taking multiple sets of three columns of associated data, that is data of x, y, z where y and z are associated with x. In building a new table with six sets of data, I put x1, x2, ..., x6 in a single column, and then spaced out y1/z1, y2/z2, ..., y6/z6 in separate columns before a sort. I then sorted on values x (all x1 to x6 in a single column).

The result is shown in the first image "pre-combine-rows"

You can see that in column "all x," many rows have identical values: for example there are five rows with value x = 218.9, and six rows with value x = 224.9. I want to combine all those rows, which combines also the values in the columns y1/z1 through y6/z6.

The result should look like the second image: "post-combine-rows"

20 rows has been combined into 5 rows.

This is a walk in the park for Excel, right? Does not even need a macro, right?

Is this one of these Database/Criteria setups?

A:Excel: Combining Rows Based on An Equal Value in One Column

Hi mavigozler,

You wouldn't require a macro to complete this feat. You can achieve the wanted result with formulas. If you still need help with this issue, I'll be glad to elaborate a formula for you.

Regards,
Peatawn

Edit: You can also have a look at the Excel matrix feature...

Read other 4 answers
RELEVANCY SCORE 59.2

Hello Forum member,

I am working on an excel file with hundreds of records and tens of columns. There is a column"L" which gives the date when the license key given in column "B" of a particular software is going to expire. I need to check the column and send email to Person X [email protected] 3 months in advance so that he can take poper action to get the license renewed. It is too much of a manual work every week.

I am looking for a macro that can send an email to person X stating that license key value given in coumn B is due to expiration in 3 months from today(based on value given in column L)

I have seem a similar thread in the forum giveb below but i need changes to it. Unfortunately, the thread is closed and i can not contact the person who gave the solution. I am not able to modify the code shared to match my requirement.
Automatic Outlook mail due date reminder based on Excel file

http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html
Can anyone please modify the code given in the above thread to match my requirement. Please!!Thank you!
 

A:Automatic Outlook mail based on a column value in excel file

Read other 8 answers
RELEVANCY SCORE 59.2

In an existing report, I need to have a count of records that have a certain text in a particular column. How do I create this command?

Example: Need a count of records in column B that have the word "yes" or "no". So I need a count of how many yes's and how many no's. I need a count for both.
 

A:Solved: Access07 - Count records based on column text

Read other 10 answers