Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2020, 12:26 AM
Danny cool Danny cool is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2013
Novice
Consolidating tables with under date
 
Join Date: Jun 2020
Posts: 5
Danny cool is on a distinguished road
Smile Consolidating tables with under date

Hi Guys,

I have a sparsely populated table that consists of a date and then seven category sizes of feed (table 1)

I have a formula for a list without blanks which condense the table. (table 2)

Problem is that i want to dates to line up for each catagory so its easy to read. For example, if there is delivery on the 29/03/20, i want it to search the other size categories to see if there are any other sizes then moved to the next column for the next delivery date. The example of what i want it too look like is in Table 3.



I believe this is quite a complex problem, tried with an IF statement but i cant get it to work inside the array formula. Do i need a macro? i would prefer a formula

Ts difficult to explain in words, have a look at the attached.

I appreciate you guys!!
Attached Files
File Type: xlsx Table sort.xlsx (257.1 KB, 6 views)
Reply With Quote
  #2  
Old 06-18-2020, 01:16 AM
Purfleet Purfleet is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I think index and match is what you are looking for

=INDEX($D$17:$BOB$23,MATCH($C43,$C$17:$C$23,0),MAT CH(D$42,$D$16:$BOB$16,0))

Do you have something to work out the dates with a delivery? if so the above seems to work

I would also reccomend turning the data around so that the feed is along the top and the dates at the side, it will be much easier to manage and read.
Reply With Quote
  #3  
Old 06-18-2020, 02:41 AM
Danny cool Danny cool is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2013
Novice
Consolidating tables with under date
 
Join Date: Jun 2020
Posts: 5
Danny cool is on a distinguished road
Default

Thanks for you reply.

Yes i think you are right. I will flip the tables around.

Its a little confusing as i may not have explained it right.

You see cells D27 to D38, there are three separate date (29/03 03/04 and 01/07) what i would need is:

column D all feed quantities arriving on the 29/03
column E all feed quantities arriving 03/04
column F all feed quantities arriving 01/07

I think i can do with two tables but i want to get it into one and i dont want to have to filter using the table drop down menu.
Reply With Quote
  #4  
Old 06-18-2020, 02:52 AM
Purfleet Purfleet is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Okay, so my attempt in D43:k49 is not what you wanted?
Reply With Quote
  #5  
Old 06-18-2020, 09:55 PM
Danny cool Danny cool is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2013
Novice
Consolidating tables with under date
 
Join Date: Jun 2020
Posts: 5
Danny cool is on a distinguished road
Default

It actually works well, thank you!

One last question. Is there anyway to get the dates in line 42 automatically?
Reply With Quote
  #6  
Old 06-18-2020, 11:03 PM
Purfleet Purfleet is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Hello mate try

Helper row in 15 - =COUNTIF(D17:23,">"&0)

I have added this in row 41 and it seems to be okay
=SMALL(IF($D$15:$BOB$15>0,$D$16:$BOB$16,""),COLUMN S($D$41:41))

Noticed you have merged cells on this work sheet and it is interferring with some formulas - be very careful of Merged cells as nothing good will ever come of them and they will cause you issues at some point.

Also row 22 was hidden
Attached Files
File Type: xlsx Table sort_Purfleet_2.xlsx (249.4 KB, 5 views)
Reply With Quote
  #7  
Old 06-20-2020, 01:42 AM
Danny cool Danny cool is offline Consolidating tables with under date Windows 10 Consolidating tables with under date Office 2013
Novice
Consolidating tables with under date
 
Join Date: Jun 2020
Posts: 5
Danny cool is on a distinguished road
Default

Purfleet, you are a legend mate thank you so much!!
Reply With Quote
Reply

Tags
consolidate, list without blanks, table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidating Rows with same Resource Name OTPM Excel Programming 2 05-30-2017 08:30 AM
Consolidating tables with under date Consolidating various word docs in one Max Downham Word 6 11-23-2015 05:07 PM
Consolidating Sentences into One Paragraph ctsolar Word 4 12-16-2013 04:50 PM
Consolidating tables with under date Consolidating data using Macro mrjamez Excel Programming 2 05-22-2012 06:50 AM
Help with consolidating multiple records into one wbiggs2 Excel 0 11-30-2006 01:02 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft