Sorting, Counting a parts list

Which version of Excel are you using? 10 is Windows, not Excel.

In Excel > File menu > at the bottom is Account.

I am not clear about what you are trying to do with the data. Is it just sorting, or are you trying to create some form of analysis (summary)?

Please update your forum profile, then answer my question in post #7.

Sort by:
Vendor
Vendor Part Number

You can do this by adding filters to the table: select row B > Data ribbon > Filter.

Create an order with the vendor stating part number and qty of each

Not enough information. Please mock up what you would want this to look like manually for a vendor so that we know what we are aiming for.

My purchase order cells and columns would define
1. Vendor Name
2. Vendor Part Number
3. Quantity
4. Price each
5. Total price for qty ordered

How to create an invoice that will take this sorted data automatically?

Mock up what you want to see in the workbook.

I can do that, but I can't seem to show this in a reply
Can't post a screenshot

In the workbook - then attach the new copy of the workbook.

Sorry - how does this relate to the first workbook you attached?

I am completely lost as to how you expect this to work.

You know your data intimitely - we don't.

I can't help unless you explain this clearly, sorry.

Please stop duplicating posts!

You asked me to post a new workbook showing the added columns

Sorry - the data in the quotation does not match the data in the original workbook as far as I can see, so I am none the wiser.

I'll leave this to someone who understands your thought processes.

Does this help?

Honestly, no - I'm sorry.

I've put out a call for help Someone will step in.

Try in A22 for the second table:

=FILTER(G4:K13,J4:J13<>"")

I still don't fully understand how you are getting to the top table - where/how are the quantities required being entered?

That is why I am reaching out for assistance, I don't know how to get the values extracted from the top table and placed into the bottom table

I've shown you how to create the bottom table from the top table in my previous post.

Fails to count how many times that part number appears in the list of 700 OEM Part Numbers

Your version above and my solution below:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E
16	HIFI COLLECTIVE	1AUTSJ330M0	$ 1.09	2	$ 2.18
17	HIFI COLLECTIVE	1CUTSJ101M0	$ 1.20	2	$ 2.40
18	HIFI COLLECTIVE	1EUTSJ100M0	$ 1.09	2	$ 2.18
19	HIFI COLLECTIVE	1HUTSJ010M0	$ 1.09	2	$ 2.18
20	HIFI COLLECTIVE	1HUTSJ3R3M0	$ 1.09	2	$ 2.18
21
22	HIFI COLLECTIVE	1AUTSJ330M0	1.09	2	2.18
23	HIFI COLLECTIVE	1CUTSJ101M0	1.2	2	2.4
24	HIFI COLLECTIVE	1EUTSJ100M0	1.09	2	2.18
25	HIFI COLLECTIVE	1HUTSJ010M0	1.09	2	2.18
26	HIFI COLLECTIVE	1HUTSJ3R3M0	1.09	2	2.18

Sheet: Sheet1

Where in your version is the count that you say is missing from mine?

You must refer to the original posted workbook

No, I'm sorry - I have asked repeatedly for everything in one workbook - I am not going to keep cross-referencing back and forth.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Hopefully someone else will be able to help you. I have to go now (it's late afternoon in the UK). Good luck!

I will take a look, but I don't want to read my way through 38 posts. Please point me to the BEST description of what you want, and the BEST sample file to look at!

Did you look in A22 in Ali's file at Post 29???

I believe you are making this far more complex than it is.

I have done my best to work out what you want from what you have given us to work with. I am sure it will be very straightforward once you adequately explain exactly what you want, how you want it and where you want it. Over to Glenn ... I'm off the laptop now.

OK. Let me get this clear. You want to pull the numbers from D41:D45 and stick them in the right place in column J. Yes or no?

If so, in the case of HIFI COLLECTIVE 1AUTSJ330M0, row 41... there are TWO possible rows in the upper table where it can go... J24 & J25. What is the RULE that tells Excel which row it should go in?

I have... and I have replied already.

That doesn't answer my question. I needed a YES or a NO and either a description of a RULE OR a correction to my interpretation.

So a guess.

J24:
=LET(A,H24:H38,IFERROR(IF(MAP(A,LAMBDA(x,COUNTIF(H24:x,x)))=1,MAP(A,LAMBDA(y,FILTER(D41:D45,B41:B45=y))),""),""))

K24:
=IFERROR(I24:I38*J24#,"")

I entered that Qty manually WHERE?? just to show what i was seeking. Out of the 700 OEM part numbers [far left] WHERE EXACTLY how many times does the HIFI Collective part number appear WHERE???(total count)

If you don't state EXPLICITLY which cell(s) we are meant to look at this is rapidly becomes a guessing game.

The BEST sample files have a CLEARLY labelled area for the BEFORE and a CLEARLY labelled area showing the desired results

For instance in one of the ten sheets I have: H4:H101 (Vendor Part Numbers)
How many: DME-170 are in this list? What is the formula to extract this count?

Use COUNTIF function

=COUNTIF(Sheet1!C:C, Part_Number)

where C is column of Part Numbers and Part_Number is the one you want to count (DME-170)

Try:

=COUNTIF(H$4:H$101,"SMC-570")

Why Part#? Surely you should be counting on the Vendor#?

Once again, please provide an updated workbook showing what you mean. Filter the data to leave the 121 rows visible.

Over to you, Ali. My Golden Rule here is I'll make only ONE guess; then I stop until a fully-explained requirement is produced.

Surely you should be counting on the Vendor#?

Sorry I used the incorrect name, Yes I am working with the Vendor #

I asked you to filter the data to the 121 rows you had left visible - please, please, please do as we ask! If you don't, it takes ten times longer to get to where we need to be!

If you don't provide exactly what I've asked for, then I shall politely withdraw my offer of further assistance.

My Golden Rule here is I'll make only ONE guess;

I'm going to give it one more go.

No - not as I requested.

I asked you to FILTER the data, not copy and paste the filtered data.

Please select row 2, Data ribbon > Filter, apply the filters that bring you to 121 rows of filtered data. Last chance!

OK - I'm done. We are getting nowhere fast. I am really sorry, but I cannot work with you on this. You keep changing your mind about how you are doing things: one minute you are asking how to filter the data (so I gave you the solution to that way back), then you filter that way, then you stop filtering that way and extract data manually - your chaotic approach is what is making it impossible to get to the crux of the matter.

I hope that someone else will be able to understand your thought processes and help you to a solution. Good luck with it.

All data shown was created using your SORT suggestion

But it doesn't show it. I asked for the data with those 'sorts' (actually filters) in place. I do not understand why you cannot simply provide what I and others have asked for.

As I said, I don't feel I can work with you as you are in fact working against me at every turn by failing to address the specific questions asked (not just by me, but by Glenn, too) and failing to provide data in the format we have requested.

80 posts in and we are no nearer to an understanding and eventual solution than we were at the start, so two experienced helpers have decided to walk away. Sorry, but I have reached the end of my usefulness to you on this. I genuinely hope that someone else can work through it with you.

in H5 and drag (copy) down

Formula:

Please Login or Register  to view this content.

???

Originally Posted by AliGW

... I do not understand why you cannot simply provide what I and others have asked for ...

Because the original file is to large to upload

You could have cut it down and still shown me data with the filters applied - not a valid excuse.

STOP quoting unnecessarily, please - it's just unnecessary clutter.

Please respond to post #81. Hopefully that's what you want.

Do I take it that John's suggestion hasn't worked for you? Please respond to his suggestion at least.

Let's be absolutely clear: you are NOT upestting anybody. Baffling us perhaps, but that's not the same thing.

ALL OF US are trying to help you, but there's only so far we can go without the information that we really need.

Once more: does John's suggestion get you any closer to what you need?

You see, you won't even answer a simple question 'yes' or 'no' - that's pretty frustrating for those trying to help.

For the fourth time, does John's suggestion in post #81 help? Yes or no?

What I want to see is a subset of your full dataset that includes perhaps THREE vendors. I then want you to use the filters to filter this dataset to leave you with the 121 rows of data you said you were going to need to count one-by-one. Let me know if you do not understand what I am asking for.

The things you take as basic are not so basic to me.

I try not to make assumptions about anyone's level of competence, but yes, it seems that you are very much a novice at Excel, so let's see if you can follow the hopefully clearer instructions in this post.

See attached if this helps: formula is in column H

UNIQUE function should get the list in columns E onwards but I do not have 365 so cannot help further.

Paste this into K5:

=UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE"))

Does it produce the correct subset of data (never mind any counting for now). Yes or no?

Does this do what you want? YES or NO?

=LET(u,UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE")),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(B:B,r))),HSTACK(u,b))

If it does, I shall explain it to you (or try to).

Look at my attachment to post #96.

Once again: does it do what you want? Please answer simply YES or NO.

OK. Now you need to try it in your real data file.

Let me try to explain it to you (I shall post shortly with an explanation).

=LET(u,UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE")),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(B:B,r))),HSTACK(u,b))

LET allows me to set parameters. The parameters are:

u > UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE"))

This filters the full data table so that it shows only rows with the vendor HIFI COLLECTIVE. UNIQUE further filters that list to unique values only.

x > INDEX(u,,2)

This indexes JUST the second column (Part #) of the filtered array.

b > BYROW(x,LAMBDA(r,COUNTIF(B:B,r)))

This applies John's COUNTIF formula to the part numbers in the filtered array.

HSTACK(u,b)

This stacks the filtered array next to the counts to give the table with the extra count column.

I hope that you'll be able to adapt this to work with your real data. All you should really need to tweak are the bits in red:

=LET(u,UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE")),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(B:B,r))),HSTACK(u,b))

Let us know once you've had a chance to try this out on your real dataset.

Unless you want an INBOX full of SPAM, don't publish your E-mail address on a public forum.

Sorry, but this needs to continue here - there is no need to take it off grid, and that would not help anyione using this thread as a source of help in the future.

If you have questions, ask them here. I shall not be communicating with you outside the forum. Thanks for your understanding and co-operation.

What is the question?

Is this what you want?

=LET(u,UNIQUE(FILTER($E$2:$G$437,$E$2:$E$437="HIFI COLLECTIVE")),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(F:F,r))),c,BYROW(x,LAMBDA(r,SUMIF(F:F,r,G:G))),HSTACK(u,b,c))

Here's a version with a drop-down list added to select the vendor.

=LET(u,UNIQUE(FILTER($E$2:$G$437,$E$2:$E$437=L2)),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(F:F,r))),c,BYROW(x,LAMBDA(r,SUMIF(F:F,r,G:G))),HSTACK(u,b,c))

It could have been done in under 10 with a clear explanation, but never mind - we got there in the end!

Next time, please try to illustrate what you are aiming for with a workbook that shows the before data and results you want manually mocked up in a sample set. That way, you'll get to where you need to be much faster.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Do the words "thank" and "you" exist in your vocabulary? If so, it would be nice if you used them instead of trying to blame everybody but yourself for the time it took us to solve this for you. You will notice, if you look around a bit, that it rarely takes us that long when members provide a proper mock-up of what they are hoping to see.

I wish you a good evening.

I gave thanks to each and everyone by "Add Reputation" and also multiple times within the Post.

Thanks to all, I do have a much better understanding of my errors in the Post #1. How would you have written the request, give me an example.

My level of appreciation is way above the scale!

How do I bring this formula into the remaining 9 sheets of this workbook?

=LET(u,UNIQUE(FILTER($E$2:$G$437,$E$2:$E$437=L2)),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(F:F,r))),c,BYROW(x,LAMBDA(r,SUMIF(F:F,r,G:G))),HSTACK(u,b,c))

I hope that you'll be able to adapt this to work with your real data. All you should really need to tweak are the bits in red:

=LET(u,UNIQUE(FILTER($A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE")),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(B:B,r))),HSTACK(u,b))

$A$2:$C$437,$A$2:$A$437="HIFI COLLECTIVE"

When I copy and paste the formula into one of the other sheets I receive this error:

When I copy and paste the formula into one of the other sheets I receive this error:

Because there is no vendor named PPT on that sheet.

=IFERROR(LET(u,UNIQUE(FILTER($E$2:$G$437,$E$2:$E$437=L2)),x,INDEX(u,,2),b,BYROW(x,LAMBDA(r,COUNTIF(F:F,r))),c,BYROW(x,LAMBDA(r,SUMIF(F:F,r,G:G))),HSTACK(u,b,c)),"No Such Vendor on this Sheet")

How do I bring this formula into the remaining 9 sheets of this workbook?

Copy and paste it. Make sure that ALL tables are in the same format. You have a hotch potch of different layouts - I'm not going to rewrite the formula to accommodate this: you need to sort it out.

For my own amusement, I had a play.

This will work if all tables start on the same row and have the same column names:

Please Login or Register  to view this content.

The bits in red have been adjusted on certain sheets. On the MAIN sheet, it only looks at the first table - you'd need to set it up lower down for the second (but I am assuming after all the examples in the attached that you can work out how to do this yourself - if not, then this may not be the way forward for you).

BUT it is a compromise that should not have to be made if all data across worksheets were 100% consistent (which is what you should be striving for).

One thing that you completely failed to mention until now was the hitch potch of data layouts and column names that exist in the real data: I can understand that you would not have considered this important, but it is, so bear it in mind if you ask any questions here in future. Full disclosure is paramount if you wish to get quick and accurate assistance.

In this version, I have set up a named range called SUMMARY:

Please Login or Register  to view this content.

This then allows me to use this formula:

=SUMMARY(headers,data,vendor)

e.g.

=SUMMARY(A3:G3,A4:G93,L2)

or:

=SUMMARY(A3:G3,A4:G93,"MOUSER")

Examples of this are on the far right of each worksheet.

This then simplifies it for you going from sheet to sheet and the layout is less important (BUT the column headers MUST match : VENDOR, VENDOR PART NUMBER & COST EACH).

You could create the same named range in any workbook to make this custom function available.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.