populate balance on userform if existed in sheet and missed in another

[abdelfattah]

Hi,
this works for jindon and I hope to getting chance help again .

I would add another condition to adjusting code for Private Sub GetData()
what I search for it if the name is new in balance first of duration sheet and is not existed in sheet1 should show in listbox1 when select all from combobox or when clear in combobox .as to select the name will show as code does it .
when there is new name in balance first of duration sheet and contains minus value then should show plus value in credit column(6) in listbox1 and if new name in balance first of duration sheet and contains plus value then should show plus value in debit column(5) as marked in picture.
thanks in advanced.

[abdelfattah]

doesn't seem there is chance to getting help for this project.:(

[jindon]

Why balalnce for only 2?

Need comppletely clear logic.
Is it only for "all"?
regrdless of dates???

[abdelfattah]

Hi jindon again ,

Why balalnce for only 2?

2 new names in balance first of duration sheet are example ,but will be many new names are transferred from previous year without they have movements in sheet1 , so I search for showing all of names for both sheets.

Is it only for "all"?

also should do that by select name and dates


regrdless of dates???


Dates should be taken into account.

[jindon]

1) What is "new"?
How do you find which lines is/are "new"?

2) How the dates should be taken account?

You should show me the result(s) that you want in workbook and explain why in various cases.

[abdelfattah]

1) What is "new"?
How do you find which lines is/are "new"?

when there are names in balance first of duration like "amer,karrem and they are not existed in sheet1 when match column B between two sheets.


2) How the dates should be taken account?

you don't need to do that , because your code does that when select all and write dates ,
the only thing what I want delete first row from showing in listbox
the first row will sum balances from balance first of duration sheet , I don't need it because listbox2 will sum them . the listbox2 will sum all of balance for both sheets , but the problem in listbox2 will not show names are existed in balance first of duration sheet and are not existed in sheet1 . the listbox2 will depends on listbox1 . so to show names in listbox2 I need to show names in balance first of duration sheet and are not existed in sheet1 should show in listbox1
based on picture delete first row and show new names in balance first of duration sheet as marked.
be careful don't change the previous conditions in your original code.
I hope the picture helps you.
thanks for your time

[jindon]

You are really confusing me.

I wrote that code as your requirement, and it was all good.


you don't need to do that , because your code does that when select all and write dates

What I meant was if the dates should be considered to create extra rows for "new" even the date are not in the range between From : To date.

As I already asked, I need to see your desired result workbook in various situations, clearly showing the logic like "new" and "delete" etc. otherwise just wasting time.

[abdelfattah]

You are really confusing me.

sorry !

As I already asked, I need to see your desired result workbook in various situations, clearly showing the logic like "new" and "delete" etc
I suppose you know the project how works that's why I just post one picture for new case .
so do you want post all of cases what project does it ,or..?

[jindon]

Since you have changed the conditions, I need to see all the different results under various circumstances.

[abdelfattah]

CASE1 when select ALL from combobox1
CASE2 when select ALL from combobox1 and dates from textboxes
CASE3 when select name from combobox1 and dates from textboxes
CASE4 when select name from combobox1
CASE5 when no selected name from combobox1 and no dates from textboxes
CASE6 when no selected name from combobox1 and write dates in textboxes
when clear combobox1 will populate all of data and when clear dates from textboxes will populate all of data if combobox1 is empty
as to listbox2
the listbox2 merge amounts for data showing in listbox1 for columns 5,6 based on name column 2 and show result in listbox2 when select items from combobox1 or write dates in textboxes.
ex: omar=2000+580000+1000+1000=584000
and when there is name in balance first of duration sheet then should add to amount where is merged and if the amount is plus then when add to amount where is merged in listbox should be in debit
ex:mariam =-5000-(100+500000)=505100

so will be in kr picture
so you can keep all of cases in listbox1 as you writing is ok but the problem will be in listbx2
the new names should show in listbox2 when they are not existed in listbox1 then should brings from balance first of duration sheet.
no need to populate in listbox1 , no need select from combobox1 , because they are not movements in sheet1 so will show as total in listbox2.
listbox2 doesn't need dates because what show in listbox1 then will merge based on specific condition as I mentioned how should be in listbox2.

[jindon]

Your pictures shows nothing difference from my original code.

As I mentioned already, if you don't post a workbook showing exact desired results on worksheet(s), not in picture, no way to understand, so ask someone else.

[abdelfattah]

Your pictures shows nothing difference from my original code.

surely yes.
and I said

so you can keep all of cases in listbox1 as you writing is ok
and the speech is around listbox2 as I said.

but the problem will be in listbx2
what I want to be show new names in listbox2 and I explain you how listbox2 collect balances for names.
the listbox2 ignores names are existed in first sheet and not existed in second sheet
the listbox2 should show new names as marked in last picture

if you don't post a workbook showing exact desired results on worksheet(s)
of course I post what you want , but the result should show on form , not on worksheet, that's why I posted pictures especially last picture
if you insist last picture should show in worksheet then here is file as highlighted by red are new names.
again listbox 2 depends on listbox1 , but when there are names are existed in first sheet , not existed in second sheet the should brings from first sheet and show in listbox2


no way to understand
honestly I do my best.


so ask someone else.
I don't think so because this is yours and about month ago of post this subject nobody answers me.

[Aussiebear]

As Jindon has kindly suggested a number of times, you will need to post a sample workbook showing what you want the workbook to look like, for each of the conditions. I'm assuming that English may not be your primary language, and as a result we are struggling to comprehend the logic of your posts.

[abdelfattah]

you will need to post a sample workbook showing what you want the workbook to look like, for each of the conditions
the result show on form , not inside sheet.
as I mentioned the matter is relating with listbox2 as long depends on listbox1 , so no need show all of conditions in listbox1 because I would keep it.
again new names should show in listbox2 as long are existed in first sheet, not existed in second sheet when collect balanced based on data in listbox1 this is based on post#10 for last picture and you can see inside sheet in post#12.

[jindon]

You said in post #1,


I would add another condition to adjusting code for Private Sub GetData()........
nothing about listbox2, and now


you can keep all of cases in listbox1 as you writing is ok....
but the problem will be in listbx2...
So, there's no guarantee that you will change what you say.
And you keep posting the same workbook...

Now I see the reason why people would hegitate to respond you post even though the problem is not so complex.

PLEASE ask some one else.

I suggest you make yourself very clear before you ask, and don't change the question within the thread.

[abdelfattah]

I suggest you make yourself very clear before you ask, and don't change the question within the thread.
my apologies
if you still interest I think this picture should be clear
in original code sum balances for all of names and put in first row in listbox1 to become -7500 in listbox1, but I would ignore sum all of names balances as code does it , just brings balances of names one by one separately without sum all of amounts. if the balance is plus then should put in debit column (5) in listbox1 and if the balance is minus then put in credit column(6) in listbox1 and do same calculation like

[abdelfattah]

I suggest you make yourself very clear before you ask, and don't change the question within the thread.
my apologies
if you still interest I think this picture should be clear
in original code sum balances for all of names and put in first row in listbox1 to become -7500 in listbox1, but I would brings balances of names one by one without sum all of amounts if the balance is plus then should put in debit column (5) and if the balance is minus then put in credit column(6) and do same calculation like (see the first 7 rows contains date 01/01/2020 brought from balance first of duration instead of sum all of names balances)
cancel sum all of names balances in first row.

[abdelfattah]

also picture is not clear?!:banghead:

[Aussiebear]

Sadly, this thread is seemly going nowhere. I would suggest that would be because the overwhelming majority of members in this forum, for reasons of their own, have no interest in the thread. Hopefully someone in one of the other vba /excel forums may be of more use to you. BTW, just for future reference, if someone politely asks you " to ask someone else for assistance", and you respond "I don't think so because this is yours and about month ago of post this subject nobody answers me", that will encourage anyone to assist you in the future. Jindon very kindly went out of his way to assist with this issue, but eventually even he found it extremely difficult to follow your concept (and that is saying something).

I am going to close this thread

[Aussiebear]

Apparently the following code is the solution


Sub GetLB2()
Dim a, e, i&, ii&, w, s$(1), t&, NewItem
If (Me.ComboBox1 = "") + (Me.ComboBox1 = "all") Then
With Sheets("sheet1")
s(0) = .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Address(, , , 1)
End With
With Sheets("balance first of duration")
s(1) = .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Address
NewItem = Filter(.Evaluate("transpose(if(isna(match(" & s(1) & "," & s(0) & ",0))," & s(1) & "))"), False, 0)
End With
End If
a = Me.ListBox1.List
With CreateObject("Scripting.Dictionary")
.Add "item", Array("ITEM", "Name", "Debit", "Credit", "Balance")
If IsArray(NewItem) Then
For Each e In NewItem
.Item(e) = Array("", e, 0, 0, 0)
Next
End If
For i = 1 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
.Item(a(i, 1)) = Array("", a(i, 1), a(i, 4), a(i, 5), a(i, 4) - a(i, 5))
Else
w = .Item(a(i, 1))
For ii = 2 To 3
w(ii) = w(ii) + a(i, ii + 2)
Next
w(4) = w(2) - w(3)
.Item(a(i, 1)) = w
End If
Next
a = Sheets("balance first of duration").[a1].CurrentRegion
For i = 2 To UBound(a, 1)
If .exists(a(i, 2)) Then
w = .Item(a(i, 2))
t = IIf(a(i, 4) > 0, 2, 3)
w(t) = w(t) + Abs(a(i, 4))
w(4) = w(2) - w(3)
.Item(a(i, 2)) = w
End If
Next
.Add "Total", Array("TOTAL", "", "", "", 0)
a = Application.Index(.items, 0, 0)
End With
mySort a
Me.ListBox2.List = a End Sub