Help to tidy up information, standardise it & spot the clashes

[ellison]

Hi, we are trying to tidy up some information where we have:
- Column A in the inputs
- Column B is the status which has been added, but needs tidying up
At the moment we use a combination of sorting information, vlookups and pot luck to try to come up with the tidied up version of the Status info, but we are wondering if there may be a better method?!
Hopefully this shows some detail about what we are trying to get at...
But if you have any questions, please just ask.

Please note we don't need the notes column automating, it's just there to help to show what we are trying to achieve in the Status-V2 column

Row	Input	Status-Orig	Notes	Status-V2
1	Alpha		Alpha: 2 blank entries and 1 x "pending", so "pending" woud be in Status V2:	pending
2	Alpha	pending	Alpha: 2 blank entries and 1 x "pending", so "pending" woud be in Status V2:	pending
3	Alpha		Alpha: 2 blank entries and 1 x "pending", so "pending" woud be in Status V2:	pending
4	Beta	completed	Beta: 0 blank entries and 1 x "completed", so "completed" would be in Status V2:	completed
5	Charlie	completed	Charlie: 1 blank entries and 1 x "completed", so "completed" would be in Status V2:	completed
6	Charlie		Charlie: 1 blank entries and 1 x "completed", so "completed" would be in Status V2:	completed
7	Delta	completed	Delta: 0 blank entries and 1 x "completed" AND 1 x "WIP", so "_clash" would be in Status V2:	_clash
8	Delta	WIP	Delta: 0 blank entries and 1 x "completed" AND 1 x "WIP", so "_clash" would be in Status V2:	_clash
9	Echo	WIP	Echo: 0 blank entries and 1 x "WIP", so "WIP" would be in Status V2:	WIP
10	Foxtrot		Foxtrot: 1 blank entries Status V2 would be blank:

 

[JEC]

On large data you could use a macro. Does not slow down your file

Sub jec()
 Dim ar, dic, i As Long
 ar = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
 Set dic = CreateObject("scripting.dictionary")
 
 For i = 1 To UBound(ar)
   If ar(i, 3) <> "" Then
     If dic.exists(ar(i, 2)) Then
       dic(ar(i, 2)) = "_Clash"
     Else
      dic(ar(i, 2)) = ar(i, 3)
     End If
   End If
 Next
 For i = 1 To UBound(ar)
   ar(i, 3) = dic(ar(i, 2))
 Next

 Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 3) = ar
End Sub

 

[Sufiyan97]

It starts with this

=ISNUMBER(SEARCH({"Pending","Completed","WIP"},D2:D11) --> gives array of true and falses

If you put this in the BYROW, it iterates from top to bottom.
Here some detail. The next step would be the substitute

Book1
	G	H	I	J	K
1	Starting point				Byrow
2
3	TRUE	FALSE	FALSE		Pending
4	TRUE	FALSE	FALSE		Pending
5	TRUE	FALSE	FALSE		Pending
6	FALSE	TRUE	FALSE		Completed
7	FALSE	TRUE	FALSE		Completed
8	FALSE	TRUE	FALSE		Completed
9	FALSE	TRUE	TRUE		CompletedWIP
10	FALSE	TRUE	TRUE		CompletedWIP
11	FALSE	FALSE	TRUE		WIP
12	FALSE	FALSE	FALSE
Sheet2

Cell Formulas
Range	Formula
G3:I12	G3	=ISNUMBER(SEARCH({"Pending","Completed","WIP"},D2:D11))
K3:K12	K3	=BYROW(G3#,LAMBDA(a,TEXTJOIN(,,FILTER({"Pending","Completed","WIP"},a,""))))
Dynamic array formulas.

Thanks JEC for explaining

How "a" works in lamda, what is that?

 

[JEC]

It is just a variable which you use to iterate through the byrow array

 

[Alex Blakenburg]

On large data you could use a macro. Does not slow down your file

I don't know if its possible but if Alpha had 2 pending lines, the code in post #11 would return _Clash.

How about changing

dic(ar(i, 2)) = "_Clash"

to

If dic(ar(i, 2)) <> ar(i, 3) Then dic(ar(i, 2)) = "_Clash"

 

[JEC]

Yes good point! So if this is possible, ellison could use your suggestion