LinkBack URL
About LinkBacks
Hi,
I have a set of data. I'm trying to extract only 1 line for each item code and invoice number and eliminate the rest. I have included a small example with my desired results below.
Thanks
Hi,
I have a set of data. I'm trying to extract only 1 line for each item code and invoice number and eliminate the rest. I have included a small example with my desired results below.
Thanks
You could do this with a helper column, and use this, copied down...
=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
From there, you could either apply filter, filter on 1 and copy/paste to destination (need to do this manually each time you need to update), or use this ARRAY function (dynamic, will update with any additions, as long as the ranges are correct)...
=IFERROR(INDEX(A$1:A$22,SMALL(IF($E$2:$E$22=1,ROW($E$2:$E$22)),ROWS($A$1:A1))),"")
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
A25=IF(COLUMNS($A$24:A24)<=COLUMNS($A$1:$D$1),IFERROR(INDEX(A$2:A$22,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$2:$C$22,$C$2:$C$22,0)),MATCH($C$2:$C$22,$C$2:$C$22,0)),ROW(A$2:A$22)-ROW(A$2)+1),ROW(A$2:A$22)-ROW(A$2)+1),ROWS($A$2:A2))),""),"")
Control+shift+enter
copy across
your custom format column D
Hi Fdibbins,
Thanks for your solution but there was a problem. I needed to make it start a new count when there was a change in amount instead of a change in item description.
The problem is that when I have the same amount later in the spreadsheet, it keeps adding to the old count when I want a new count to start. Is there
any way around this? I added a new file newtest.xlsx so you can see what I mean.
Thanks
I typed in A25
=IF(COLUMNS($A$24:A24)<=COLUMNS($A$1:$D$1),IFERROR(INDEX(A$2:A$22,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$2:$C$22,$C$2:$C$22,0)),MATCH($C$2:$C$22,$C$2:$C$22,0)),ROW(A$2:A$22)-ROW(A$2)+1),ROW(A$2:A$22)-ROW(A$2)+1),ROWS($A$2:A2))),""),"")
and didn't get any result?
After you type in the formula, don't just press enter.
Instead, press CTRL + SHIFT + ENTER
If you've already entered the formula, then highlight the cell with the formula and press F2.
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}
Last edited by CARACALLA; 06-12-2020 at 10:28 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks