Condition 1
E column : Construction order No , Corresponding to column B
Condition 2
F column : Construction order , Corresponding to column C
Find out that column A is not repeatedly placed in G2:T6 ranges
Any help would be appreciated !
Condition 1
E column : Construction order No , Corresponding to column B
Condition 2
F column : Construction order , Corresponding to column C
Find out that column A is not repeatedly placed in G2:T6 ranges
Any help would be appreciated !
Last edited by Sakurayuki; 12-27-2020 at 09:31 AM.
Hi, you should take a ook at the built in COUNTIFS() Function and the INDIRECT() function.
It can easily done with VBA but in Formulas it will be a little more complicated.
Last edited by Keebellah; 12-24-2020 at 03:51 AM.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Hi Sakurayuki,
Try below code ...
Sub test() Dim a, j$, Dic As Object, i& a = [A1].CurrentRegion Set Dic = CreateObject("scripting.dictionary") For x = 2 To UBound(a) j = Join(Array(a(x, 2), a(x, 3)), ";") If Not Dic.exists(j) Then Dic.Add j, a(x, 1) Else If InStr(Dic(j), a(x, 1)) = 0 Then Dic(j) = Dic(j) & ";" & a(x, 1) If InStr(Dic(j), ";") > 1 Then If UBound(Split(Dic(j), ";")) + 1 > i Then i = UBound(Split(Dic(j), ";")) + 1 Next With [E2].Resize(Dic.Count) .Offset(-1).Resize(1, 2) = [{"Construction order No","Construction order"}] .Offset(-1, 2).Resize(1, i) = "Order number" .Value = Application.Transpose(Dic.keys) .TextToColumns [E2], semicolon:=True .Offset(, 2) = Application.Transpose(Dic.items) .Offset(, 2).TextToColumns [G2], semicolon:=True .Offset(-1).Resize(, i + 2).Columns.AutoFit End With End Sub
Last edited by nankw83; 12-24-2020 at 03:57 AM.
If I was able to help, you can thank me by clicking the * Add Reputation under my user name
Just for the fun of it...Here is an array option...For large data set Dict is better though...
Sub sintekJ3v16() Dim X, Data, Result, i As Long, ii As Long, iii As Long, xx As Long Data = Cells(1).CurrentRegion.Value ReDim Result(1 To UBound(Data), 1 To UBound(Data)) For i = 2 To UBound(Data) X = Application.Match(Data(i, 2), Application.Index(Result, , 1), 0) If Not IsNumeric(X) Then ii = ii + 1: xx = ii: iii = 0: Result(ii, 1) = Data(i, 2): Result(ii, 2) = Data(i, 3) Else xx = X If Not IsNumeric(Application.Match(Data(i, 1), Application.Index(Result, ii), 0)) Then iii = iii + 1: Result(xx, iii + 2) = Data(i, 1) Next i With Range("E1") .Resize(, 2) = Array("Construction Order No", "Construction Order") .Offset(, 2).Resize(, iii) = "Order No" .Offset(1).Resize(ii, iii + 2) = Result .CurrentRegion.Columns.AutoFit End With End Sub
Last edited by Sintek; 12-24-2020 at 08:19 AM.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
I appreciate both of your answers,
Thank you very much both for providing the code, the question is finally answered satisfactorily, you are both too good
Glad to help & thanks for the added Rep.
.........................
Thanks.png
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks