Loops And Counters 


Loops And Counters  Excel 
View Answers 
Half the battle with this one is in describing the problem . . .
If someone could help with getting me started; I’d like to know how to use a loop counter (ForNext) in conjunction with a second counter that adjusts the integer on each loop. I'm hoping that describes the problem but, if not, maybe this explains it better:
I have many columns of data – only three rows high  containing all kinds of combinations of numerics.
Initially, I’m looping though to find the first occurrence of a particular combination:
Row1 = 2.2
Row2 = 3.4
Row3 = 4.6
(When I find that combination I copy, transpose and write it to another sheet. I can do that part.)
Next loop ‘round I want to ignore the above – as I already have the first occurrence of that sequence – and instead find the first occurrence of:
Row1 = 2.1
Row2 = 3.4
Row3 = 4.6
And after that:
Row1 = 2.0
Row2 = 3.4
Row3 = 4.6
Finally – here’s the painful part – I want another loop to do similar things with the second number. For example:
Row1 = 2.2
Row2 = 4.0
Row3 = 4.6
But I’ll worry about that part later . . .
Any guidance  especially on the first stage with a loping counter and a variable counter  very gratefully received!
Cheers.
If someone could help with getting me started; I’d like to know how to use a loop counter (ForNext) in conjunction with a second counter that adjusts the integer on each loop. I'm hoping that describes the problem but, if not, maybe this explains it better:
I have many columns of data – only three rows high  containing all kinds of combinations of numerics.
Initially, I’m looping though to find the first occurrence of a particular combination:
Row1 = 2.2
Row2 = 3.4
Row3 = 4.6
(When I find that combination I copy, transpose and write it to another sheet. I can do that part.)
Next loop ‘round I want to ignore the above – as I already have the first occurrence of that sequence – and instead find the first occurrence of:
Row1 = 2.1
Row2 = 3.4
Row3 = 4.6
And after that:
Row1 = 2.0
Row2 = 3.4
Row3 = 4.6
Finally – here’s the painful part – I want another loop to do similar things with the second number. For example:
Row1 = 2.2
Row2 = 4.0
Row3 = 4.6
But I’ll worry about that part later . . .
Any guidance  especially on the first stage with a loping counter and a variable counter  very gratefully received!
Cheers.
Similar Excel Video Tutorials
VBA lesson 2 for loops
 ExcelExperts.com brings you VBA lessons. This one is lesson number 2 on FOR loops ...
Helpful Excel Macros
Delete Duplicate Rows
 This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
 This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Delete a VBA Module From Excel
 Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
 Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Capitalize the First Letter of Every Word in a Cell
 This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in
 This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in
Complete Guide to Printing in Excel Macros  PrintOut Method in Excel
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Format Cells in The Number (Numerical) Number Format in Excel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Similar Topics
Hey again guys,
I need to find a way for the IF function to perform some tricky calculations (at least for me). I have three rows. If the value in one row is less than half of the value of the other two rows, then it is true.
Here is an example:
row1 row2 row3
38 53 93
We see that row1 is less than half of row3 but not of row2.
But..in this other case:
row1 row2 row3
53 21 81
Row2 is less than half of either of the other two. How would I create a formula to do that, please? Thanks!
I need to find a way for the IF function to perform some tricky calculations (at least for me). I have three rows. If the value in one row is less than half of the value of the other two rows, then it is true.
Here is an example:
row1 row2 row3
38 53 93
We see that row1 is less than half of row3 but not of row2.
But..in this other case:
row1 row2 row3
53 21 81
Row2 is less than half of either of the other two. How would I create a formula to do that, please? Thanks!
I have a problem that is likely very simple, but which has stumped me. I have a list of values that look like the following:
Row1 Col1 Value
Row1 Col2 Value
Row1 Col3 Value
Row1 Col4 Value
Row2 Col1 Value
Row2 Col2 Value
Row2 Col3 Value
Row2 Col4 Value
Row3 Col1 Value
Row3 Col2 Value
etc.
What I really want is:
...........Col1...Col2...Col3...Col4
Row1 Value Value Value Value
Row2 Value Value Value Value
Row3 Value Value Value Value
Row4 Value Value Value Value
etc.
Is there any easy way to do this easily? Sometimes I have up to 500 rows and columns, so manual cutandpaste is out of the question. Thanks in advance for any assistance.
Row1 Col1 Value
Row1 Col2 Value
Row1 Col3 Value
Row1 Col4 Value
Row2 Col1 Value
Row2 Col2 Value
Row2 Col3 Value
Row2 Col4 Value
Row3 Col1 Value
Row3 Col2 Value
etc.
What I really want is:
...........Col1...Col2...Col3...Col4
Row1 Value Value Value Value
Row2 Value Value Value Value
Row3 Value Value Value Value
Row4 Value Value Value Value
etc.
Is there any easy way to do this easily? Sometimes I have up to 500 rows and columns, so manual cutandpaste is out of the question. Thanks in advance for any assistance.
Hi,
I am trying to see if I can do the following in Excel......
Column A
Row1 5/09/2008 8:28
Row2 5/09/2008 8:28
Row3 5/09/2008 8:28
Row4 5/09/2008 8:28
Column B
Row1 is empty
Row2 is empty
Row3 RWLU214846
Row4 RWTU9711617
Column C
Row1 RWTU9627105
Row2 CRXU0778382
Row3 is empty
Row4 is empty
Column D
Row1 5/09/2008 8:48
Row2 5/09/2008 8:48
Row3 5/09/2008 8:48
Row4 5/09/2008 8:48
Column E
Row1 VSW640
Row2 VSW640
Row3 VSW640
Row4 VSW640
Column F
Row1 5814851
Row2 5814851
Row3 5814851
Row4 5814851
I am after a formula that will give the following results in Column H, I
The above data may be 200 or more rows, with different data, but most times there will be 2, 3 or 4 rows, with data in Column F being identical. This is the reference point.
Take Column D from Column A result Column H, ie in this case 00:20 minutes
As there are 4 rows that have an identical number in Column F, count the cells in Columns B & C that fall in the same 4 rows that have data in, result in Column I. ie in this case 4
Any Help appreciated
Cfer
I am trying to see if I can do the following in Excel......
Column A
Row1 5/09/2008 8:28
Row2 5/09/2008 8:28
Row3 5/09/2008 8:28
Row4 5/09/2008 8:28
Column B
Row1 is empty
Row2 is empty
Row3 RWLU214846
Row4 RWTU9711617
Column C
Row1 RWTU9627105
Row2 CRXU0778382
Row3 is empty
Row4 is empty
Column D
Row1 5/09/2008 8:48
Row2 5/09/2008 8:48
Row3 5/09/2008 8:48
Row4 5/09/2008 8:48
Column E
Row1 VSW640
Row2 VSW640
Row3 VSW640
Row4 VSW640
Column F
Row1 5814851
Row2 5814851
Row3 5814851
Row4 5814851
I am after a formula that will give the following results in Column H, I
The above data may be 200 or more rows, with different data, but most times there will be 2, 3 or 4 rows, with data in Column F being identical. This is the reference point.
Take Column D from Column A result Column H, ie in this case 00:20 minutes
As there are 4 rows that have an identical number in Column F, count the cells in Columns B & C that fall in the same 4 rows that have data in, result in Column I. ie in this case 4
Any Help appreciated
Cfer
Hi,
I have the following cell formula:
Code:
Which checks two rows to meet the above conditions.
Now I want to write a code where every row in the worksheet is compared to all rows in the worksheet for the above conditions.
So row1 with row2, row3, row4...
then row2 with row1, row3, row4...
then row3 with row1, row2, row4...
Thanks guys for your help!
cheers
Tobi
I have the following cell formula:
Code:
=IF(AND(D1=D2, C1=C2, B1B2, E1E2), TRUE, FALSE)
Which checks two rows to meet the above conditions.
Now I want to write a code where every row in the worksheet is compared to all rows in the worksheet for the above conditions.
So row1 with row2, row3, row4...
then row2 with row1, row3, row4...
then row3 with row1, row2, row4...
Thanks guys for your help!
cheers
Tobi
Hi All,
I am receiving a quite large number of tab separated text files with
information each week (50+), that I need to import into an Excel sheet and
then work with=produce reports
It is quite time consuming to run the File import utility for each file, and
I would therefore like to create a macro assigned to a button that should do
the following.
For each text file in a folder, sorted by file name or creation date, do the
following:
1. Import the tab separated data, and append it to the already existing data
in the active sheet. The information should be imported like the example below
2. Move the file to another folder, an "archive"
If I have this I can easily create a rule in Outlook that stores my text
files in a certain folder, open excel, push the button, and be ready to work.
Would be great!
The file that should be imported looks like this:
Header1
Header2
Colummn Header 1 Column Header 2 Column Header 3
ROW11 ROW12 ROW13
ROW21 ROW22 ROW23
ROW31 ROW32 ROW33
Footer1
Footer2
Footer3
Ideally, I would like the excel spreadsheet to look like this:
Header2 ROW11 ROW12 ROW13
Header2 ROW21 ROW22 ROW23
Header2 ROW31 ROW32 ROW33
I have tried to write macros for this but cannot make it work. If all cannot
be done (I realize this might be quite complex) I would appreciate any help
with getting as close as possible to the ultimate solution!
Many thanks,
David
I am receiving a quite large number of tab separated text files with
information each week (50+), that I need to import into an Excel sheet and
then work with=produce reports
It is quite time consuming to run the File import utility for each file, and
I would therefore like to create a macro assigned to a button that should do
the following.
For each text file in a folder, sorted by file name or creation date, do the
following:
1. Import the tab separated data, and append it to the already existing data
in the active sheet. The information should be imported like the example below
2. Move the file to another folder, an "archive"
If I have this I can easily create a rule in Outlook that stores my text
files in a certain folder, open excel, push the button, and be ready to work.
Would be great!
The file that should be imported looks like this:
Header1
Header2
Colummn Header 1 Column Header 2 Column Header 3
ROW11 ROW12 ROW13
ROW21 ROW22 ROW23
ROW31 ROW32 ROW33
Footer1
Footer2
Footer3
Ideally, I would like the excel spreadsheet to look like this:
Header2 ROW11 ROW12 ROW13
Header2 ROW21 ROW22 ROW23
Header2 ROW31 ROW32 ROW33
I have tried to write macros for this but cannot make it work. If all cannot
be done (I realize this might be quite complex) I would appreciate any help
with getting as close as possible to the ultimate solution!
Many thanks,
David
I have an array of data in a table and I want to convert it into a list [see attached pic for clarity]...
What I have:
Col1 Col2 Col3
Row1 xx ss tt
Row2 yy rr uu
Row3 zz qq jj
What I want:
ListCol1 ListCol2 ListCol3
Row1 Col1 xx
Row1 Col2 ss
Row1 Col3 tt
Row2 Col1 yy
Row2 Col2 rr
Row2 Col3 uu
Row3 Col1 zz
Row3 Col2 qq etc...
I know that once I have the values set up in ListCol1 and ListCol2, I can use an =INDEX(..,MATCH(ListCol1..),MATCH(ListCol2),...) sort of setup to get the values for ListCol3. My problem is how do I fill in the first two columns?
Thanks in advance,
Rich
What I have:
Col1 Col2 Col3
Row1 xx ss tt
Row2 yy rr uu
Row3 zz qq jj
What I want:
ListCol1 ListCol2 ListCol3
Row1 Col1 xx
Row1 Col2 ss
Row1 Col3 tt
Row2 Col1 yy
Row2 Col2 rr
Row2 Col3 uu
Row3 Col1 zz
Row3 Col2 qq etc...
I know that once I have the values set up in ListCol1 and ListCol2, I can use an =INDEX(..,MATCH(ListCol1..),MATCH(ListCol2),...) sort of setup to get the values for ListCol3. My problem is how do I fill in the first two columns?
Thanks in advance,
Rich
Hi Guys,
I have been trying to sort a twodimensional array in excel using VBA.
the values of the array are all strings not numbers, which has been causing my head to explode.
I have an excel sheet that contains data 4 rows by 4 columns.
I then want to put that data in to an array so I can use it.
This is the first time I have been playing with 2D Arrays in VBA. From what I gathered this is how it should look:
Code:
so going by this principal i tried to sort the array by the 2nd column. I tried various examples from other sites but they all say that "subscript out of range"
can anyone please let me know if I am going about this completely the wrong way. Any help greatly appreciated. Thanks
Code:
I have been trying to sort a twodimensional array in excel using VBA.
the values of the array are all strings not numbers, which has been causing my head to explode.
I have an excel sheet that contains data 4 rows by 4 columns.
I then want to put that data in to an array so I can use it.
This is the first time I have been playing with 2D Arrays in VBA. From what I gathered this is how it should look:
Code:
Dim MyArray(1 To 4, 1 To 4) MyArray(1, 1) = "A col1 row1" MyArray(1, 2) = "C col1 row2" MyArray(1, 3) = "D col1 row3" MyArray(1, 4) = "B col1 row4" MyArray(2, 1) = "D col2 row1  sort this col" MyArray(2, 2) = "B col2 row2  sort this col" MyArray(2, 3) = "A col2 row3  sort this col" MyArray(2, 4) = "C col2 row4  sort this col" MyArray(3, 1) = "A col3 row1" MyArray(3, 2) = "D col3 row2" MyArray(3, 3) = "B col3 row3" MyArray(3, 4) = "C col3 row4" MyArray(4, 1) = "D col4 row1" MyArray(4, 2) = "C col4 row2" MyArray(4, 3) = "B col4 row3" MyArray(4, 4) = "A col4 row4"
so going by this principal i tried to sort the array by the 2nd column. I tried various examples from other sites but they all say that "subscript out of range"
can anyone please let me know if I am going about this completely the wrong way. Any help greatly appreciated. Thanks
Code:
Dim MyArray(1 To 4, 1 To 4) Dim i As Integer, j As Integer MyArray(1, 1) = "A col1 row1" MyArray(1, 2) = "C col1 row2" MyArray(1, 3) = "D col1 row3" MyArray(1, 4) = "B col1 row4" MyArray(2, 1) = "D col2 row1  sort this col" MyArray(2, 2) = "B col2 row2  sort this col" MyArray(2, 3) = "A col2 row3  sort this col" MyArray(2, 4) = "C col2 row4  sort this col" MyArray(3, 1) = "A col3 row1" MyArray(3, 2) = "D col3 row2" MyArray(3, 3) = "B col3 row3" MyArray(3, 4) = "C col3 row4" MyArray(4, 1) = "D col4 row1" MyArray(4, 2) = "C col4 row2" MyArray(4, 3) = "B col4 row3" MyArray(4, 4) = "A col4 row4" SortColumm1 = 1 SortColumn2 = 3 For i = LBound(MyArray, 1) To UBound(MyArray, 1)  1 For j = LBound(MyArray, 1) To UBound(MyArray, 1)  1 Condition1 = MyArray(j, SortColumn1) > MyArray(j + 1, SortColumn1) Condition2 = MyArray(j, SortColumn1) = MyArray(j + 1, SortColumn1) And _ MyArray(j, SortColumn2) > MyArray(j + 1, SortColumn2) If Condition1 Or Condition2 Then For y = LBound(MyArray, 2) To UBound(MyArray, 2) t = MyArray(j, y) MyArray(j, y) = MyArray(j + 1, y) MyArray(j + 1, y) = t Next y End If Next Next
Hi Experts,
I am a novice in using VBA or Macros in excel. I need a help in copying all the available rows from Individual Sheet say A, B & C that needs to be copied to Sheet D in the given below fashion in a Worksheet:
Copy Row1 from Sheet A
Copy Row1 from Sheet B
Copy Row1 from Sheet C
Output Results in Sheet D should be:
Row1 = Paste Row1 from Sheet A
Row2 = Paste Row1 from Sheet B
Row3 = Paste Row1 from Sheet C
Row4 = Paste Row2 from Sheet A
Row5 = Paste Row2 from Sheet B
Row6 = Paste Row2 from Sheet C
I have around 4224 rows in Sheets A, B & C that needs to be copied to Sheet D. Hope i have explained what i am looking at above clearly. If you need any clarifications then, please let me know.
Your help would be greatly appreciated...
Thanking a lot in advance...
Thanks & Regards,
Kaushal
I am a novice in using VBA or Macros in excel. I need a help in copying all the available rows from Individual Sheet say A, B & C that needs to be copied to Sheet D in the given below fashion in a Worksheet:
Copy Row1 from Sheet A
Copy Row1 from Sheet B
Copy Row1 from Sheet C
Output Results in Sheet D should be:
Row1 = Paste Row1 from Sheet A
Row2 = Paste Row1 from Sheet B
Row3 = Paste Row1 from Sheet C
Row4 = Paste Row2 from Sheet A
Row5 = Paste Row2 from Sheet B
Row6 = Paste Row2 from Sheet C
I have around 4224 rows in Sheets A, B & C that needs to be copied to Sheet D. Hope i have explained what i am looking at above clearly. If you need any clarifications then, please let me know.
Your help would be greatly appreciated...
Thanking a lot in advance...
Thanks & Regards,
Kaushal
Hi All,
In Excel 2007 sheet i have a column whose type is text. It contains date time stamp in chronological order but the difference of time between two adjacent rows is not same. What i mean is this:
Row1: 20110121 09:17:33.0
Row2: 20110121 09:18:33.0
Row3: 20110121 09:27:33.0
Row4: 20110121 09:29:33.0
Row5: 20110121 09:33:33.0
Row6: 20110121 09:38:33.0
Row7: 20110121 09:47:33.0
Row8: 20110121 09:51:33.0
Row9: 20110121 09:60:33.0
Row10: 20110121 09:66:33.0
....
...
The time difference between Row1 value and Row2 value is approx. 1min.
The time difference between Row2 value and Row3 value is approx. 9min.
The time difference between Row3 value and Row4 value is approx. 2min.
The time difference between Row4 value and Row5 value is approx. 4min.
so on...
Now i want a new coulmn that has following values in its rows:
Row1: 0
Row2: 1
Row3: 10
Row4: 12
Row5: 16
so on...
As you can see the values addup in every next row.
Can anyone guide me how it can be done? Any help would be great.
naeemdotcom
In Excel 2007 sheet i have a column whose type is text. It contains date time stamp in chronological order but the difference of time between two adjacent rows is not same. What i mean is this:
Row1: 20110121 09:17:33.0
Row2: 20110121 09:18:33.0
Row3: 20110121 09:27:33.0
Row4: 20110121 09:29:33.0
Row5: 20110121 09:33:33.0
Row6: 20110121 09:38:33.0
Row7: 20110121 09:47:33.0
Row8: 20110121 09:51:33.0
Row9: 20110121 09:60:33.0
Row10: 20110121 09:66:33.0
....
...
The time difference between Row1 value and Row2 value is approx. 1min.
The time difference between Row2 value and Row3 value is approx. 9min.
The time difference between Row3 value and Row4 value is approx. 2min.
The time difference between Row4 value and Row5 value is approx. 4min.
so on...
Now i want a new coulmn that has following values in its rows:
Row1: 0
Row2: 1
Row3: 10
Row4: 12
Row5: 16
so on...
As you can see the values addup in every next row.
Can anyone guide me how it can be done? Any help would be great.
naeemdotcom
I have:
Col ACol BCol C
row1 cow cat
row2 dog
row3 sheep
I need:
For all rows, if Col B has data but Col C does not, move data from Col B into Col A and down one row
Col ACol BCol C
row1 cow cat
row2
row3 dog
row4 sheep
Hi,
This is my first ever post as I'm new to VBA, so forgive any sloppy code or inadvertant breach of etiquette.
My problem is I’m not able to influence the ending row (where the loop stops) of a ‘For’ loop to reflect rows inserted during its execution.
I am writing a spreadsheet macro where there will be a different number of rows each time it is run. My macro correctly determines the final row and then runs a ‘For’ loop to process every row.
When I encounter a change of, in this case a contract number I insert a line and I then increment my counter and the variable holding the row count by one.
The problem is that the execution still stops when the current row is equal to the starting value of the variable final row – stopping short of the last row. How do / can I influence where the loop stops once I’m in the loop?
Code:
This is my first ever post as I'm new to VBA, so forgive any sloppy code or inadvertant breach of etiquette.
My problem is I’m not able to influence the ending row (where the loop stops) of a ‘For’ loop to reflect rows inserted during its execution.
I am writing a spreadsheet macro where there will be a different number of rows each time it is run. My macro correctly determines the final row and then runs a ‘For’ loop to process every row.
When I encounter a change of, in this case a contract number I insert a line and I then increment my counter and the variable holding the row count by one.
The problem is that the execution still stops when the current row is equal to the starting value of the variable final row – stopping short of the last row. How do / can I influence where the loop stops once I’m in the loop?
Code:
Dim FinalRow As Integer Dim i As Long . . FinalRow = Cells(65536, 1).End(xlUp).Row For i = 2 To FinalRow . . i = i + 1 Cells(i, 1).Select ActiveCell.EntireRow.Insert shift:=xlDown FinalRow = FinalRow + 1 . . Next i
dear everyone,
I have 3 columns say
colA colB Col C
row1 1 A pen
row2 2 B sit
row3 1 A read
now i want my output to be like
colA colB Col C
row1 1 A pen
row2 read
row3 2 B sit
that is i want to group all the As in the column B and allow it to display
only once for howmany ever rows we have in col C.
The main thing is that these rows get filled from another sheet. therefore
they are dynamic, i may have any number of rows in this sheet.
I want to do this kind of grouping inside a macro. kindly help
I have 3 columns say
colA colB Col C
row1 1 A pen
row2 2 B sit
row3 1 A read
now i want my output to be like
colA colB Col C
row1 1 A pen
row2 read
row3 2 B sit
that is i want to group all the As in the column B and allow it to display
only once for howmany ever rows we have in col C.
The main thing is that these rows get filled from another sheet. therefore
they are dynamic, i may have any number of rows in this sheet.
I want to do this kind of grouping inside a macro. kindly help
Thread Locked as Title infringes forum rules
I have an excel chart ( XY scatter, compares pairs of values.)
How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)
for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)
for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)
and so on...
Thanks for any help anybody can give...
Vivek
I have an excel chart ( XY scatter, compares pairs of values.)
How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)
for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)
for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)
and so on...
Thanks for any help anybody can give...
Vivek
I have a csv file with roughly 4k rows. I am trying to avoid doing this process manually. What I need is to duplicate every row and change a few values systematically. So real world example is something like this:
Row1
Row2
Row3
Row4
Becomes
Row1
Row1copy
Row2
Row2copy
Row3
Row3copy
Row4
Row4copy
Then I need to
delete the values from columns B,C,D,F from just the copied rows.
delete the value from column H and change it to "SCAN" on just the copied rows
append "scan" to the end of column M on just the copied rows
column S value needs do be deleted and changed to 6.99 on just the copied rows
columns U and V need to be deleted and both changed to "FALSE"
column W value needs to be deleted.
Is something this complicated even possible? And if so where, oh where do I start?
Any and all tips/advice is greatly appreciated. If there is a service that can build this as a (macro?, function?) for a fee that would even work.
Thanks! and Best!
Jamie
Row1
Row2
Row3
Row4
Becomes
Row1
Row1copy
Row2
Row2copy
Row3
Row3copy
Row4
Row4copy
Then I need to
delete the values from columns B,C,D,F from just the copied rows.
delete the value from column H and change it to "SCAN" on just the copied rows
append "scan" to the end of column M on just the copied rows
column S value needs do be deleted and changed to 6.99 on just the copied rows
columns U and V need to be deleted and both changed to "FALSE"
column W value needs to be deleted.
Is something this complicated even possible? And if so where, oh where do I start?
Any and all tips/advice is greatly appreciated. If there is a service that can build this as a (macro?, function?) for a fee that would even work.
Thanks! and Best!
Jamie
Hi Folks,
I use Access to extract relevant data via queries and create tables. Excel then retrieves the data from the tables made in Access (via the ‘Data – Import External Data – Import Data’ method). I use Excel’s ‘External Data’ Toolbar to ‘Refresh All’ in the Excel workbook. I have formula on several worksheets which I would like to protect from accidental changes. THE PROBLEM IS: If I apply protection (‘Tools – ProtectionProtect Worksheet’), the ‘Refresh All’ control on the ‘External Data’ Toolbar does not work.
If anyone knows how to resolve this problem I would be very pleased to hear from you and exceedingly grateful. I am not an expert in Excel and so will probably need step by step explanations of how to carry out whatever suggestions you kindly offer. David M Wright
I use Access to extract relevant data via queries and create tables. Excel then retrieves the data from the tables made in Access (via the ‘Data – Import External Data – Import Data’ method). I use Excel’s ‘External Data’ Toolbar to ‘Refresh All’ in the Excel workbook. I have formula on several worksheets which I would like to protect from accidental changes. THE PROBLEM IS: If I apply protection (‘Tools – ProtectionProtect Worksheet’), the ‘Refresh All’ control on the ‘External Data’ Toolbar does not work.
If anyone knows how to resolve this problem I would be very pleased to hear from you and exceedingly grateful. I am not an expert in Excel and so will probably need step by step explanations of how to carry out whatever suggestions you kindly offer. David M Wright
I have an excel chart ( XY scatter, compares pairs of values.)
How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)
for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)
for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)
and so on...
Thanks for any help anybody can give...
Vivek
How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)
for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)
for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)
and so on...
Thanks for any help anybody can give...
Vivek
Question 1:
I would like to alert the user if a line of data does not adhere to the following validation rules listed below. The user will then need to manually correct the data and the template will not perform any autocorrection of the data for the user.
a) Header – Check that ‘PODAgentDetails’ exists.
b) SO – Check that the length is 10 digits.
c) SR – Check that the length is 11 characters long (inclusive of ‘SR’ characters)
d) CourierDay & PODDay – Check that the date is in the correct mm/dd/yyyy format. It will also check if that the ‘mm’ field is between 1 to 12.
e) CourierTime & PODTime – Check that the time is in the correct hh:mi:ss format. Check that ‘hh’ is between 0 to 23, ‘mi’ is between 0 to 59 and ‘ss’ is between 0 to 59
Question 2:
The input of the data to this excel sheet, might be key in or paste. How to protect the cell validation rules if we paste one row or column data on the it.
Question 3:
Any suggestion how to let the user manually see the problem of the key in or paste data format is wrong? ( maybe auto checking  circle/colour the cell)
Please download the attachment and have a look.
Thanks for all suggestion for helping.
I would like to alert the user if a line of data does not adhere to the following validation rules listed below. The user will then need to manually correct the data and the template will not perform any autocorrection of the data for the user.
a) Header – Check that ‘PODAgentDetails’ exists.
b) SO – Check that the length is 10 digits.
c) SR – Check that the length is 11 characters long (inclusive of ‘SR’ characters)
d) CourierDay & PODDay – Check that the date is in the correct mm/dd/yyyy format. It will also check if that the ‘mm’ field is between 1 to 12.
e) CourierTime & PODTime – Check that the time is in the correct hh:mi:ss format. Check that ‘hh’ is between 0 to 23, ‘mi’ is between 0 to 59 and ‘ss’ is between 0 to 59
Question 2:
The input of the data to this excel sheet, might be key in or paste. How to protect the cell validation rules if we paste one row or column data on the it.
Question 3:
Any suggestion how to let the user manually see the problem of the key in or paste data format is wrong? ( maybe auto checking  circle/colour the cell)
Please download the attachment and have a look.
Thanks for all suggestion for helping.
Hi,
I would like to select cell based on the result of other cells in a different row. I have 3 rows and 12 coulombs. Row1 is the number of hours, Row2 is the energy used, Row3 is the result of Row1 divided by Row2 which is a ratio. The 12 coulombs are number 1 to 12 and are the months in the year. I need to be able to find the lowest ratio result in Row3 and then based on that result select the corresponding cell in the same coulomb but in Row1.
Any help would be much appreciated
Thanks Bernard
I would like to select cell based on the result of other cells in a different row. I have 3 rows and 12 coulombs. Row1 is the number of hours, Row2 is the energy used, Row3 is the result of Row1 divided by Row2 which is a ratio. The 12 coulombs are number 1 to 12 and are the months in the year. I need to be able to find the lowest ratio result in Row3 and then based on that result select the corresponding cell in the same coulomb but in Row1.
Any help would be much appreciated
Thanks Bernard
Hi,
I'm not sure how to describe what I am trying to do in excel. Please no VBA.
I have 3 sheets. #1 and #2 have items listed in a column. #3 is a summary, like below. I want sheet #3 to display items that are in one list, but not in the other...
Here is my example:
sheet #1  unique values
ColumnA

Row1: apples
Row2: oranges
Row3: pears
Row4: strawberries
Row5: bananas
Row6: mango
sheet #2  repeat values are possible
ColumnA

Row1: oranges
Row2: bananas
Row3: grapes
Row4: grapefruit
Row5: kiwi
Row6: strawberies
Row7: pears
Row8: oranges
Row9: oranges
Row10: kiwi
sheet #3
ColumnA: items in sheet 1 (A1:A6), but not in sheet 2 (A1:A10):
Row1: apples
Row2: mango
ColumnB: items in sheet 2 (A1:A10), but not in sheet 1 (A1:A6):
Row1: grapes
Row2: grapefruit
Row3: kiwi
* Do not repeat "kiwi" even though it is found 2 times in sheet #2 and none on sheet #1
thank you for your patience.
I'm not sure how to describe what I am trying to do in excel. Please no VBA.
I have 3 sheets. #1 and #2 have items listed in a column. #3 is a summary, like below. I want sheet #3 to display items that are in one list, but not in the other...
Here is my example:
sheet #1  unique values
ColumnA

Row1: apples
Row2: oranges
Row3: pears
Row4: strawberries
Row5: bananas
Row6: mango
sheet #2  repeat values are possible
ColumnA

Row1: oranges
Row2: bananas
Row3: grapes
Row4: grapefruit
Row5: kiwi
Row6: strawberies
Row7: pears
Row8: oranges
Row9: oranges
Row10: kiwi
sheet #3
ColumnA: items in sheet 1 (A1:A6), but not in sheet 2 (A1:A10):
Row1: apples
Row2: mango
ColumnB: items in sheet 2 (A1:A10), but not in sheet 1 (A1:A6):
Row1: grapes
Row2: grapefruit
Row3: kiwi
* Do not repeat "kiwi" even though it is found 2 times in sheet #2 and none on sheet #1
thank you for your patience.
I was wondering if anybody could help me. Here is my problem:
Row1 ? Row2 10 Row3 20 Row4 Sum(Row1:Row3) I want to make Row1 equal to a number that is always going to make my sum equal to 1.5, how can I do that? That would not only create a circular reference but I am not sure about the formula.
Thanks for the help,
Mark
Row1 ? Row2 10 Row3 20 Row4 Sum(Row1:Row3) I want to make Row1 equal to a number that is always going to make my sum equal to 1.5, how can I do that? That would not only create a circular reference but I am not sure about the formula.
Thanks for the help,
Mark
I do have a database I’m trying to build and have a problem – In our plant we do small maintenance work on the machines (tasks) – in order to track these tasks I created a spreadsheet that lists the tasks and when the operator completes the task – they hit a macro button that simply dates the task and files it away in a data tab – reseting the field for the next entry – then I take a pivot table for the data tab and summarize the data – what is up to date and what is behind –
My problem is how do I create a sheet that the operator can click completed on the task and submit the data to the other workbook – resetting the task for next time. WITHOUT USING MACROS  everyone is worried about macros and not being able to see how they work in case i move departments  I want to make the data store step more visible (simple)  I saw userforms but don’t think that is my best option. Any help is greatly appreciated.
My problem is how do I create a sheet that the operator can click completed on the task and submit the data to the other workbook – resetting the task for next time. WITHOUT USING MACROS  everyone is worried about macros and not being able to see how they work in case i move departments  I want to make the data store step more visible (simple)  I saw userforms but don’t think that is my best option. Any help is greatly appreciated.
Hi,
I will appreciate your help on my project. As shown in the attached file, I have data from Columns A to G. I want to do the following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in columns B, C and D are also duplicates (The condition is all 4 columns must have the same values)
3. If the condition in (2) is satisfied, compare column E for all the rows; select the row with the minimum value in column E, and delete the other rows.
For example:
A B C D E F G
Row1:Test01 100 Class 1 1 80.67 6.47 145
Row2:Test01 50 Class 1 3 80.25 6.71 598
Row3:Test01 100 Class 1 1 77.72 6.62 110
Since Rows 1 and 3 have the same values in columns A, B, C and D; and Row3 value in column E is less than Row1 value (77.72<80.67); Delete Row1
Results
A B C D E F G
Row2:Test01 50 Class 1 3 80.25 6.71 598
Row3:Test01 100 Class 1 1 77.72 6.62 110
Thank you for your help!
Hi,
I tried to find a macro on this forum which can transpose a set of rows to columns, but could not find one that solves my needs. Can anyone help me out? Much thanks in advance
Problem description (sample Excel 2010 File attached):
> Excisting Data
Row1: WireWay
Row2: Manufacturer of wiremanagement control systems
Row3: http://www.wireway.com
>Row4: Empty Row
Row5: Marrow Lin Development Co., Ltd.
Row6: Manufacturer of copper terminals
Row7: http://www.marrowlin.com
>Row8: Empty Row
...next till end
> Need to become:
Row1>Column1: WireWay
Row1>Column2: Manufacturer of wiremanagement control systems including modular
Row1>Column3: http://www.wireway.com
Row2>Column1: Marrow Lin Development Co., Ltd.
Row2>Column2:Manufacturer of copper terminals
Row2>Column3:http://www.marrowlin.com
I tried to find a macro on this forum which can transpose a set of rows to columns, but could not find one that solves my needs. Can anyone help me out? Much thanks in advance
Problem description (sample Excel 2010 File attached):
> Excisting Data
Row1: WireWay
Row2: Manufacturer of wiremanagement control systems
Row3: http://www.wireway.com
>Row4: Empty Row
Row5: Marrow Lin Development Co., Ltd.
Row6: Manufacturer of copper terminals
Row7: http://www.marrowlin.com
>Row8: Empty Row
...next till end
> Need to become:
Row1>Column1: WireWay
Row1>Column2: Manufacturer of wiremanagement control systems including modular
Row1>Column3: http://www.wireway.com
Row2>Column1: Marrow Lin Development Co., Ltd.
Row2>Column2:Manufacturer of copper terminals
Row2>Column3:http://www.marrowlin.com
EXAMPLE:
Row2 A2:12 B2:25 C2:36 D2:48 E2:56 G2:56
Row3 A3:1 B3:0 C3:1 D3:2 E3:4
Row2 shows 5 different numbers
Row3 relates to the Row2 number, Row 3 is the number of times Row2 number has hit.
I'm looking for a formula which picks the "highest" hit number (Row3) and places the appropriate Row2 number in the G2 cell.
This would be an automatic update since hits per number increase over time.
Thanks for the help...
Row2 A2:12 B2:25 C2:36 D2:48 E2:56 G2:56
Row3 A3:1 B3:0 C3:1 D3:2 E3:4
Row2 shows 5 different numbers
Row3 relates to the Row2 number, Row 3 is the number of times Row2 number has hit.
I'm looking for a formula which picks the "highest" hit number (Row3) and places the appropriate Row2 number in the G2 cell.
This would be an automatic update since hits per number increase over time.
Thanks for the help...
Hi guys,
I have a problem with VBA I need to overcome.
OK, so, I have three Worksheets:
Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table
What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.
So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:
31/07/2010
07/08/2010
14/08/2010
21/08/2010
28/08/2010
04/09/2010
Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.
I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.
I’m sure this is simple buy I’m struggling with it. Thanks in advance.
I have a problem with VBA I need to overcome.
OK, so, I have three Worksheets:
Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table
What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.
So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:
31/07/2010
07/08/2010
14/08/2010
21/08/2010
28/08/2010
04/09/2010
Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.
I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.
I’m sure this is simple buy I’m struggling with it. Thanks in advance.