Mapping multiplying problem

[Marcus_230]

Hello all,

Would someone be as kind as to assist me with mapping from other sheets please? I am having an issue with mapping, where my entries are being multiplied. I am not certain, where I am making a mistake. I am attaching a short sample,

Quick description: data are being pulled from a database with 2 tables. Both tables have a unique ID " ID ". As you can see, dollar amounts are being copied onto additional rows.

Thank you.

M

[HansV]

See the attached version.

mapping_data_problem.xlsx

[Marcus_230]

Thank you very much for the quick response. Unfortunately it will not work in my case.

[HansV]

Please explain why not.

[Marcus_230]

When I enter the calculations into my mapped project, it causes issue, #SPILL!, but when I start building it from scratch, it works. So let me rebuilt the project and I will report back. Just a quick question: Is the $ and # necessary at the end after before B and after 3? ? =SUMIFS(Table2!E$2:E$30,Table2!$A$2:$A$30,$B3#)

Thank you.

M

[HansV]

The $ are necessary because you want to be able to fill (drag) the formula from C3 to E3.
The # ensures that the formulas in C3, D3 and E3 will spill to the same number of rows as the formula in B3.

[Marcus_230]

Good morning,

thank you for the explanation. Like I stated yesterday, unfortunately it will not work. When I use the recommended calculation in my excel template for my database, and then I run it I get an error, the file runs, but the payments, deposits, and totals, are not aligned with the correct IDs. I am not sure, if there is another way. I certainly have not idea.
Thank you.

[Marcus_230]

So, I took another look at my own database and I think I know where the issue is, regardless of the error output. The ID and the values are correct, not the additional columns I have between. Please see attached and I would like to ask you, how do I get the Numbers and dates in the column C, D, E precisely lined up with the IDs?
Thank you.

[HansV]

On the third sheet, each ID is listed only once.
On the first sheet, multiple numbers and dates are associated with a single ID. How would you want those to be listed on the third sheet?

[Marcus_230]

I have probably created an incorrect/ example. When I look at my original database, there are multiple rows with the same ID and Numbers. The reason is, that there are several differently named values on payment and deposits sides. So ultimately my goal is to have the unique ID and tied payments, deposits, and totals, which is now perfect on the workbook. I would like to have the correct number and dates tied to the ID. Do you want me to make another example?

[HansV]

Please explain HOW you would like the numbers and dates tied to the unique IDs.

[Marcus_230]

Yes, I will submit better example. I apologizes. Give me a few more minutes please.

[Marcus_230]

Attached is the new example. As you can see the ID and the Number with dates are in the Table1. I would like to have the Number, dates and name on the ResultProblem sheet. On my end I was getting doubles, which you had eliminated, but when I tried to link the number, dates, and name, it was a mismatch. It was not linked to the unique(filtered) ID.
Thank you.

[HansV]

See the attached version.

mapping_data_problem_version 2.xlsx

[Marcus_230]

After the unique IDs are displayed on the " ResultProblem " Tab, I would like to have the Number, Dates, Name, Payments, Deposits, and Total tied from the Table2, resulting in no doubles and IDs will be tied to the correct data from Table 2.
Thank you.

[Marcus_230]

This works beautifully. Unfortunately when I run this via my software database as a template with updated data, they system does not like it. So it is the system and there is nothing I can do. I would like to thank you very much for your assistance. Very much appreciated. I will have to find out another way.

[Marcus_230]

If I may ask, I noticed in my database, that there other numbers assigned to the same ID. I understand, that now we have a Unique ID, but what in this case, where the unique number has 3 more numbers assign to it?
Thank you.

[HansV]

Which version of Excel does that system use?

[Marcus_230]

I am using 365. I do not know. I will find out. When I upload the template and run it, I will get errors, repairs and I see only formulas on these inside the columns: LET(V, XLOOKUP......

[HansV]

Functions such as XLOOKUP are only available in Excel in Microsoft 365 and Office 2021. So if the system uses Office 2019 or older, the formulas won't work.