How to extract entries to a table while merging the values of the duplicate ones

Hello forum,

Before i jump to my question, i would like to mention that i looked for a thread that covers a similar issue, but couldn't find one.

So i do have 2 sheets. In the 1st one there is a list of new expenses Screenshot_3.png. What i am being asked is to add these expenses to the existing lists in the 2nd sheet. The lists that have been created there are three, each one including personnel based on the expenses they made this month (first list covers the >150 range, the second one the 99-149 range and the third one the <100 range) Screenshot_4.png.

Now, as you can see in Sheet 1, some people that have to be added, have already made expenses for this month, while others appear for the first time. So how am i supposed to add all of these people to the corresponding lists on sheet 2 based on the expenses they made (they should be extracted to the matching range according to their expenses)? Moreover, how am i supposed to do that while summing the expenses of the personnel that appear for the second time Screenshot_5.png.

Unfortunately, i have to keep the preexisting format with the lists as given in the 2nd sheet.

Thank you in advance!

Let's say John Yellow's expense in sheet1 is 50 then the total would be 160, then it should move from List 2 to List 1, right?
Try this:
Sheet2 must be the activesheet when you run the code.

Please Login or Register  to view this content.

With John Yellow's expense in sheet1 is 50, here's the result:

Excel 2013 32 bit

	A	B	C	D
1	List 1
2	ID	NAME	DEPARTMENT	Expenses >150
3	7099	Tony Brown	Finance	600.00 €
4	4122	Tom Black	Finance	500.00 €
5	4456	Mary Red	Finance	320.00 €
6	2222	Tim Purple	Marketing	180.00 €
7	1003	John Yellow	HR	160.00 €
8	4520	Sasha Grey	Marketing	300.00 €
9
10
11
12	List 2
13	ID	NAME	DEPARTMENT	Expenses 100~150
14	2004	Jim Green	HR	130.00 €
15	7089	Sam Blue	Logistics	130.00 €
16	3669	Fiona Pink	Marketing	125.00 €
17
18
19
20	List 3
21	ID	NAME	DEPARTMENT	Expenses <100
22	4600	Catherine Orange	Logistics	45.00 €
23	5002	Susan White	Marketing	70.00 €
24	6045	Orpheus Burgundy	Logistics	45.00 €
25	4669	Maria Cyan	HR	50.00 €
26	9977	Solomon Gold	Finance	40.00 €

Sheet: Sheet2

Wow that helped a lot, thanks.

Sorry for the delayed answer, my schedule has been rough these couple of days.

You're welcome, glad to help, & thanks for the feedback.