Match Row Count by Key Column - Two Tables

[NewYears1978]

Hey all, hope you're having a nice week.

I have two tables on a sheet that are the same (well the layout). However in the second table the key value (First column) appears more times than in the first table. I am trying to match the number of rows for each unique value so add blank rows into the first table.

I have tried a separate table with the counts of number of rows to add then using VBA but that would crash. I tried PowerQuery but couldn't figure out how to get it how I need it. I tried pivot tables. I am at my end here.

I am attaching a sample sheet with small set of data, keep in mind the real set is very large. The desired result is on the second tab.

Thanks in advance!

[alansidman]

See attached and advise if this works for you in PQ.

[NewYears1978]

See attached and advise if this works for you in PQ.

This looks similar to something I tried but failed in, going to give it a look and see if it looks correct (on glance it does).
I assume those first rows with nothing beside them means those had no matching rows in the second table?

Thanks so much, I will report back once I have a chance to look into it further later.

[NewYears1978]

So this almost worked, not sure if I did something wrong. On my left side (Syspro ID) the number is appearing in the last blank instead of the first. See attached image.

My tables were named LeftTable and RightTable (I know, original) and my query looked like this:

Please Login or Register  to view this content.

Thanks for your help

(I did rename my tables to the same Table1 and Table2 and use your exact query, with the same results. Not sure why it's different. In your sample file they are indeed in the right order that I want)

I can see why the left is a little off. It's matching the SKU and putting the row on the same line as the matching SKU - which unfortunately doesn't quite work. I need the start of rows to start with the Syspro ID so it's easy to compare one to the other (data from one table to the other)

I also tried

Please Login or Register  to view this content.

But then in the left side data it just fills the same data from the row across all blanks til the next Unique Syspro ID.

Okay I think I got it now by sorting both Syspro ID columns =D

Please Login or Register  to view this content.

Sorry for all the rambling lol.

[alansidman]

Glad to hear it worked out. Thanks for the Rep

[NewYears1978]

Funny - tried to reproduce this in a second file and can't get them to sort properly again =D

[alansidman]

Post a representative example of the second file and see if we can resolve.

[NewYears1978]

Here's a new sample.

I tried hours to get this to work and cannot. It's so weird because the result works in one of my files, but in a second IDENTICAL file that I did the same exact process (I even just copied the exact queries out of it), it doesn't work. They don't sort. I get all the blank rows first instead, next to the none blank rows - instead of the data next to each other.

[JeteMc]

I believe that the difference is that Alan saved queries to table1 and table2 as connections only whereas you didn't.
See if Merge2, produced from the table1 (2) and table2 (2) connections, is showing the expected results.
Note that I don't believe that there are any records from table2 that match both Syspro ID and SKU from table1.
Let us know if you have any questions.

[NewYears1978]

The SKUS are not supposed to match. I might not have been clear on that. The SKUS from Table1 are based off a list of legacy skus and the second list are other skus that were not those legacy skus.

So in Merge2 the results are also not correct.

Yesterday I was finally able to get a VBA macro working that sorted this out onto a new sheet the way I needed so I am all good now. Still a little mindblown on the PQ stuff though and why it worked one time but doing it again the same way another time did not work..haha.

Not sure if doing a table as a connection or not matters, the data is still the same so the merge query should be the same regardless, right?

Thanks all for the assist!

[JeteMc]

When matching only the ID's in PQ this is the result.
Let us know if you have any questions.

[NewYears1978]

When matching only the ID's in PQ this is the result.
Let us know if you have any questions.

Yes, this is the original way I did it but this copies down the line above data over all blanks. It's close but should leave blanks =D