Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula problem
If I have data laid out as follows
Col1 Col2 Col3 Col4 Name1 111 tyr uuu Name1 222 tyr uuu Name1 333 tyr uuu Name2 111 tyr uuu Name2 222 tyr uuu Name3 111 tyr uuu Name4 111 tyr uuu Name4 222 tyr uuu How can I use a formula in a new column to get the following output Col1 Col2 Col3 Col4 Col5 1 Name1 111 tyr uuu 1 Name1 222 tyr uuu 1 Name1 333 tyr uuu 2 Name2 111 tyr uuu 2 Name2 222 tyr uuu 3 Name3 111 tyr uuu 4 Name4 111 tyr uuu 4 Name4 222 tyr uuu the name in the above column 2 will always be the same they will always be in consecutive rows as I will sort first. I dont know if this is possible but if it is it will save me a lot of time :- Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula problem
Hi Andrew,
Assuming your data starts in B2, and A1 is not a number, enter this in A2 and copy it down. =IF(B2=B1,A1,A1+1) Hope this works for you, Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula problem
Hi Andrew,
I am not certain that this will accomplish what you need, but it appears that you want to put a count in column A. This will number the names, assuming the names are grouped and sorted. Sub Macro1() Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "No" Range("A2").Select ActiveCell.Value = 1 Ct = 1 Do Until ActiveCell.Offset(0, 1).Value = "" No1 = ActiveCell.Offset(0, 1).Value No2 = ActiveCell.Offset(1, 1).Value NoMinus = ActiveCell.Offset(-1, 1).Value If ActiveCell.Address = "$A$2" Then ActiveCell.Value = 1: ActiveCell.Offset(1, 0).Select: GoTo Bottomloop If No1 = No2 Then If No1 = NoMinus Then ActiveCell.Value = Ct Else ActiveCell.Value = Ct End If Else If No1 = NoMinus Then ActiveCell.Value = Ct Ct = Ct + 1 Else ActiveCell.Value = Ct Ct = Ct + 1 End If End If ActiveCell.Offset(1, 0).Select Bottomloop: Loop End Sub Thanks, "Andrew" wrote: If I have data laid out as follows Col1 Col2 Col3 Col4 Name1 111 tyr uuu Name1 222 tyr uuu Name1 333 tyr uuu Name2 111 tyr uuu Name2 222 tyr uuu Name3 111 tyr uuu Name4 111 tyr uuu Name4 222 tyr uuu How can I use a formula in a new column to get the following output Col1 Col2 Col3 Col4 Col5 1 Name1 111 tyr uuu 1 Name1 222 tyr uuu 1 Name1 333 tyr uuu 2 Name2 111 tyr uuu 2 Name2 222 tyr uuu 3 Name3 111 tyr uuu 4 Name4 111 tyr uuu 4 Name4 222 tyr uuu the name in the above column 2 will always be the same they will always be in consecutive rows as I will sort first. I dont know if this is possible but if it is it will save me a lot of time :- Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem | Excel Worksheet Functions | |||
IF-Then formula problem | Excel Worksheet Functions | |||
Formula Problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Discussion (Misc queries) |