Search for value and return value from previous row

[kazman101]

HI All

Wondering if anyone can help, I have an excel sheet that has a list of status transitions, in the sheet there are system generated transitions because a user has forgotten to perform an action

I need to pull out a list of the userids involved which is in Column I, however if the system performs a transaction it shows the system userID "AUTO_UPDATE" the actual user ID is contained in either the previous row or the following row

does anyone know of a way I can put together a list of these users

 

[hiker95]

kazman101,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[kazman101]

Hi There

Thanks for the warm welcome, I'm currently using Excel 2007, I've followed your suggestion and attached a sample fo the data I have and what I am looking to acheive

 

[kazman101]

Hi There

Thanks for the warm welcome, I'm currently using Excel 2007,

Below is data from 4 columns, the status is in the third column and the user id is the 4th column, I'm looking to find the user ID of all instances where the Status was Auto-Suspend or where the user Id = AUTO_UPDATE


Application Full Started UK_skanchan
Application Full Auto-Suspend AUTO_UPDATE
Application Full Resume UK_skanchan
Application Full Finished UK_skanchan
Consumer_Email Short Started UK_skanchan
Consumer_Email Short Auto-Suspend AUTO_UPDATE
Consumer_Email Short Resume UK_skanchan
Consumer_Email Short Finished UK_skanchan
Customisation_Settings Delta Started UK_skanchan
Customisation_Settings Delta Finished UK_skanchan
Customisation_variant 1 Delta Started UK_skanchan
Customisation_variant 1 Delta Auto-Suspend AUTO_UPDATE
Customisation_variant 1 Delta Resume UK_skanchan
Customisation_variant 1 Delta Finished UK_skanchan
Exploratory Short Started UK_skanchan
Exploratory Short Auto-Suspend AUTO_UPDATE
Exploratory Short Resume UK_skanchan

 

[hiker95]

kazman101,

You have just pasted some text. It is difficult to see where your data resides in reference to the workskeet it is in.

And, you have not displayed an area where your output is to go.


You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[kazman101]

sorry about that, guess excel does not cop/paste well

the file is at the following link

http://www.box.net/shared/1djpbjk53ixsbifor1r1

 

[hiker95]

kazman101,

Thanks for the workbook.

the actual user ID is contained in either the previous row or the following row

Can the actual user ID always be found in the previous row and always be found in the following row?

 

[kazman101]

Thanks for looking at it

Yes the UserID will always be in that location, the system orders it like that automatically

 

[hiker95]

kazman101,


Sample raw data in worksheet Status Transitions:

Excel Workbook
	A	B	C	D	E	F	I	J
1	Vendor	Commercial Name	Testcycle	Testarea	Test Type	Effort Type	User	*
2	*	*	Acc-1/Acc 1	3G_Handset	Delta	LocalAddOn	UK_nrohilla	*
3	*	*	Acc-1/Acc 1	3G_Handset	Delta	LocalAddOn	UK_nrohilla	*
4	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
5	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
6	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
7	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
8	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
9	*	*	Acc-1/Acc 1	Application	Delta	LocalAddOn	UK_RJoseph	*
10	*	*	Acc-1/Acc 1	Exploratory	Delta	LocalAddOn	UK_RJoseph	*
11	*	*	Acc-1/Acc 1	Exploratory	Delta	LocalAddOn	UK_RJoseph	*
12	*	*	Acc-1/Acc 1	Exploratory 2	Delta	LocalAddOn	UK_RJoseph	*
13	*	*	Acc-1/Acc 1	Exploratory 2	Delta	LocalAddOn	UK_RJoseph	*
14	*	*	MR-5/MR1 for Gingerbread	3G_Handset	Short	LocalAddOn	UK_nrohilla	*
15	*	*	MR-5/MR1 for Gingerbread	3G_Handset	Short	LocalAddOn	UK_nrohilla	*
16	*	*	MR-5/MR1 for Gingerbread	Exploratory	Short	LocalAddOn	UK_mponnuswamy	*
17	*	*	MR-5/MR1 for Gingerbread	Exploratory	Short	LocalAddOn	UK_mponnuswamy	*
18	*	*	MR-2/QMR1	3G_Handset	Short	LocalAddOn	UK_nrohilla	*
19	*	*	MR-2/QMR1	3G_Handset	Short	LocalAddOn	UK_nrohilla	*
20	*	*	MR-2/QMR1	Exploratory 2	Short	LocalAddOn	UK_mponnuswamy	*
21	*	*	MR-2/QMR1	Exploratory 2	Short	LocalAddOn	UK_mponnuswamy	*
22	*	*	Acc-3/Acc 3	3G_Handset	Delta	LocalAddOn	UK_nrohilla	*
23	*	*	Acc-3/Acc 3	3G_Handset	Delta	LocalAddOn	UK_nrohilla	*
24	*	*	Acc-3/Acc 3	Application	Delta	LocalAddOn	UK_skanchan	*
25	*	*	Acc-3/Acc 3	Application	Delta	LocalAddOn	UK_RJoseph	*
26	*	*	Acc-3/Acc 3	Application	Delta	LocalAddOn	UK_skanchan	*
27	*	*	Acc-3/Acc 3	Application	Delta	LocalAddOn	UK_skanchan	*
28	*	*	Acc-3/Acc 3	Exploratory 1b*	Delta	LocalAddOn	UK_mponnuswamy	*
29	*	*	Acc-3/Acc 3	Exploratory 1b*	Delta	LocalAddOn	UK_mponnuswamy	*
30	*	*	Acc-3/Acc 3	MR Sanity tests	Delta	LocalAddOn	UK_mponnuswamy	*
31	*	*	Acc-3/Acc 3	MR Sanity tests	Delta	LocalAddOn	UK_mponnuswamy	*
32	*	*	Acc-3/Acc 3	Previous Issues	Delta	LocalAddOn	UK_mponnuswamy	*
33	*	*	Acc-3/Acc 3	Previous Issues	Delta	LocalAddOn	UK_mponnuswamy	*
34	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla	*
35	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	AUTO_UPDATE	*
36	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla	*
Status Transitions

After the first macro FindAU, a new filter is applied to the new column J Search, for AUTO_UPDATE, and we get this:

Excel Workbook
	A	B	C	D	E	F	I	J
1	Vendor	Commercial Name	Testcycle	Testarea	Test Type	Effort Type	User	Search
34	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla	AUTO_UPDATE
52	*	*	Acc-1/Acc1	GPRS Desk	Full	LocalAddOn	UK_nrohilla	AUTO_UPDATE
72	*	*	Acc-1/Acc1	SAT	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
84	*	*	Acc-1/Mac OS X Lion preview	Exploratory	Delta	LocalOnly	UK_RJoseph	AUTO_UPDATE
102	*	*	Acc-1/MR1	Application	Full	LocalAddOn	UK_skanchan	AUTO_UPDATE
106	*	*	Acc-1/MR1	Consumer_Email	Short	LocalAddOn	UK_skanchan	AUTO_UPDATE
112	*	*	Acc-1/MR1	Customisation_variant 1	Delta	LocalAddOn	UK_skanchan	AUTO_UPDATE
116	*	*	Acc-1/MR1	Exploratory	Short	LocalAddOn	UK_skanchan	AUTO_UPDATE
124	*	*	MR-2/MR2	Exploratory	Delta	LocalAddOn	UK_skanchan	AUTO_UPDATE
130	*	*	Acc-1/ACC1/R5AA002	Application	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
136	*	*	Acc-1/ACC1/R5AA002	Content_Types	Full	LocalAddOn	UK_aradhakrishnan	AUTO_UPDATE
140	*	*	Acc-1/ACC1/R5AA002	Customisation_Settings	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
144	*	*	Acc-1/ACC1/R5AA002	Exploratory	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
154	*	*	Acc-1/ACC1/R5AA002	GSM_and_Basic_Operations	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
160	*	*	Acc-1/ACC1/R5AA002	SAT	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
164	*	*	Acc-1/ACC1/R5AA002	User_Experience	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
174	*	*	Acc-1	Application	Full	LocalAddOn	UK_aradhakrishnan	AUTO_UPDATE
180	*	*	Acc-1	Customisation_Settings	Full	LocalAddOn	UK_mponnuswamy	AUTO_UPDATE
186	*	*	Acc-1	MMS	Short	LocalAddOn	UK_mponnuswamy	AUTO_UPDATE
190	*	*	Acc-1	Previous Issues	Short	LocalAddOn	UK_mponnuswamy	AUTO_UPDATE
204	*	*	Acc-2	3G_Handset	Short	LocalAddOn	UK_nrohilla	AUTO_UPDATE
212	*	*	Acc-2	Customisation_Settings	Full	LocalAddOn	UK_mponnuswamy	AUTO_UPDATE
216	*	*	Acc-2	GSM - Screened Room	Short	LocalAddOn	UK_nrohilla	AUTO_UPDATE
222	*	*	Acc-2	Previous Issues	Short	LocalAddOn	UK_mponnuswamy	AUTO_UPDATE
228	*	*	MR-2/MR2	Application	Full	LocalAddOn	UK_aradhakrishnan	AUTO_UPDATE
236	*	*	Acc-2/CAF	Application	Delta	LocalAddOn	UK_skanchan	AUTO_UPDATE
240	*	*	Acc-2/CAF	Customisation_Settings	Delta	LocalAddOn	UK_RJoseph	AUTO_UPDATE
268	*	*	Acc-3/Acc-3	Previous Issues	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
276	*	*	*	*	*	*	*	*
Status Transitions

Then you manually apply the filter in column C, Testcycle, for Acc-1/Acc1 ,and we get this:

Excel Workbook
	A	B	C	D	E	F	I	J
1	Vendor	Commercial Name	Testcycle	Testarea	Test Type	Effort Type	User	Search
34	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla	AUTO_UPDATE
52	*	*	Acc-1/Acc1	GPRS Desk	Full	LocalAddOn	UK_nrohilla	AUTO_UPDATE
72	*	*	Acc-1/Acc1	SAT	Full	LocalAddOn	UK_ggarg	AUTO_UPDATE
276	*	*	*	*	*	*	*	*
Status Transitions

Then we run the next macro STCopyAU, that creates worksheet Expected if it does not exist, or clears the sheet first, and then we get this:

Excel Workbook
	A	B	C	D	E	F	G
1	Vendor	Commercial Name	Testcycle	Testarea	Test Type	Effort Type	User
2	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla
3	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_nrohilla
4	*	*	Acc-1/Acc1	3G_Handset	Full	LocalAddOn	UK_ggarg
5	*	*	*	*	*	*	*
Expected

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindAU()
' hiker95, 07/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=565303
Dim wS As Worksheet, wE As Worksheet
Dim c As Range, firstaddress As String
Application.ScreenUpdating = False
Set wS = Worksheets("Status Transitions")
wS.AutoFilterMode = False
wS.Columns("J").ClearContents
wS.Range("J1") = "Search"
With wS.Columns("I")
  Set c = .Find("AUTO_UPDATE", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      c.Offset(-1, 1).Value = "AUTO_UPDATE"
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wS.UsedRange.AutoFilter Field:=10, Criteria1:="AUTO_UPDATE"
Application.ScreenUpdating = True
End Sub


Sub STCopyAU()
' hiker95, 07/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=565303
Dim wS As Worksheet, wE As Worksheet
Dim LR As Long, NR As Long, RC As Long
Application.ScreenUpdating = False
Set wS = Worksheets("Status Transitions")
If Not Evaluate("ISREF(Expected!A1)") Then Worksheets.Add(After:=wS).Name = "Expected"
Set wE = Worksheets("Expected")
wE.UsedRange.Clear
With wE.Range("A1:G1")
  .Value = [{"Vendor","Commercial Name","Testcycle","Testarea","Test Type","Effort Type","User"}]
  .Font.Bold = True
End With
LR = wS.Cells(Rows.Count, "C").End(xlUp).Row
On Error Resume Next
RC = 0
RC = Application.Subtotal(103, wS.Range("C1:C" & LR)) - 1
If RC > 1 Then
  NR = wE.Range("C" & Rows.Count).End(xlUp).Offset(1).Row
  wE.Range("A" & NR).Resize(RC, 6).Value = wS.Range("A1:F" & LR).Offset(1).Resize(wS.Rows.Count - 1).SpecialCells(12).Value
  wS.Range("I1:I" & LR).Offset(1).Resize(wS.Rows.Count - 1).SpecialCells(12).Copy wE.Range("G" & NR)
End If
wE.UsedRange.Columns.AutoFit
wE.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the two macros, first macro FindAU, and in one of the other columns in the data filter, make a choice, and run the second macro STCopyAU that moves the found data to worksheet Expected.


The macro can be adjusted so that the data in worksheet Expected is not deleted, but added to.

 

[kazman101]

Thanks very much that works brilliantly, thanks for your help