Power Query- unique merge situation

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
full disclosure, I am using power query in Excel. I also don't know if what I want to do is possible or practical.

We have a client that forces us to use a web portal to invoice them. Unfortunately for us, this web portal is terrible. we have to select their reference numbers out of the portal and for most of our loads (we are in trucking), we will need two reference numbers. The idea is supposed to be specific identification , but it doesn't really work that way and instead we select reference numbers based upon dates (our loads show completion on date X so we attempt to grab two reference numbers from date X. If not enough are available we will grab others around that date. long story short, we regularly get wrapped around the axle and billing is very difficult. I am trying to sort out and arrange everything up front before we begin billing. I have built a bot to do the actual billing for us, but I still have to get everything outlined first.

My question: I have a table with a list of transactions on it. Each transaction has a date. Each transaction will require two reference numbers. On another table I have a list of reference numbers that also have a date. The count of reference numbers required and count of reference numbers available is pretty much always different. I would like to be able to merge the available reference numbers to available spots without duplicating any numbers. Once this is done I can do an anti join to then get a list of unassigned reference numbers that I can arbitrarily assign as required.

Has anyone done any thing like this before? If so, how would you go about it if you thin kit was possible?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,328
I would like to be able to merge the available reference numbers to available spots without duplicating any numbers. Once this is done I can do an anti join to then get a list of unassigned reference numbers that I can arbitrarily assign as required.
did you try this?
it's work for you?
what is the main question?
are you able to post an example (with generic data but with original structure) of your source data and expected result?

too little practical information and too much theory ;)
 
Last edited:

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
The below images encapsulate what I'm trying to do. I did not include all data as it is far too much to practically put here and to be honest none of the other fields are pertinent to the problem. I hope this communicates the issue well enough. The Other info image should help to interpret the desired results image.

Edit: the merge is not a normal merge. I don't know what the proper terminology would be but its kind of a fuzzy logic version where I want you to fill up the potential spots on the transaction with references numbers to the extent they are available by date. Its not a typical 1 to 1 merge like would normally be used. The only real key we have is the dates on both tables and that ends up being a many to many relationship.


table1- transactions.png
table 2- reference numbers.png
Desired results.png


Other Info.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,328
sorry mate but pictures are useless, cannot be used to edit or something.
use xl2bb to post copyable data
 

jrg24

New Member
Joined
Aug 6, 2010
Messages
49

ADVERTISEMENT

example.xlsx
AB
1Table1- Transactions
2TransIdDate
31234568/4/2020
41234578/4/2020
51234588/4/2020
61234598/4/2020
71234608/4/2020
81234618/4/2020
91234628/4/2020
101234638/3/2020
111234648/3/2020
121234658/3/2020
131234668/3/2020
141234678/2/2020
151234688/2/2020
161234698/2/2020
171234708/2/2020
181234718/2/2020
191234728/2/2020
20
Table1- Transactions


example.xlsx
AB
1Table2- Reference numbers
2Reference numbersDate
39408108/4/2020
43444868/4/2020
51278908/4/2020
64006738/4/2020
79285168/4/2020
84952868/4/2020
99828668/4/2020
106659318/4/2020
115691478/4/2020
126276038/4/2020
134921548/4/2020
141964598/4/2020
154966828/4/2020
162248448/3/2020
172843768/3/2020
186622898/3/2020
195532668/3/2020
205492808/3/2020
217161798/3/2020
222680598/3/2020
236288808/3/2020
247749308/3/2020
251332828/2/2020
268807198/2/2020
275654188/2/2020
281582598/2/2020
295467658/2/2020
303201348/2/2020
311069378/2/2020
324519978/2/2020
338645638/2/2020
Table2- Reference numbers


example.xlsx
ABCD
1Desired output
2From Table 1From Table 2
3TransIdDateRef 1Ref 2
41234568/4/2020940810665931
51234578/4/2020344486569147
61234588/4/2020127890627603
71234598/4/2020400673492154
81234608/4/2020928516196459
91234618/4/2020495286496682
101234628/4/2020982866
111234638/3/2020224844549280
121234648/3/2020284376716179
131234658/3/2020662289268059
141234668/3/2020553266628880
151234678/2/2020133282106937
161234688/2/2020880719451997
171234698/2/2020565418864563
181234708/2/2020158259
191234718/2/2020546765
201234728/2/2020320134
Desired output


example.xlsx
GHIJKLM
24Other Info
25This is only for ease of explanation and is not a part of the desired output. I don’t care of the output is pivoted like it is here, I just did that for ease of demonstration
26
27DateTransaction CountReference numbers neededReference numbers availableReference numbers usedOver/ShortNotes
288/4/20207141313-1Needed 14, only 13 available, one spot left null
298/3/202048981one more reference provided than number than needed, only 8 added to final output
308/2/202061299-3Needed 12, only 9 provided, three spots left null
31Total17343130-3All in, we were able to use 30 of the 31 provided reference numbers
Desired output
 

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
I found what I need. I found a way to index by group that should allow what I need to do. I will index each item by date, which ill then give me a unique key to do my merge. Thanks for your help
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,107
Messages
5,509,259
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top